Re: Converting integer time

Stan,

Since SQL 2005 and earlier only has a datetime data type, then your time
needs to have a date. You can ignore the date as needed and deal only with
the time.

SELECT DATEADD(second, MySecondsColumn, 0);

Assuming that there were 12312 seconds, this would return:
1900-01-01 03:35:12.000

If you want to add the seconds to today's date, then:

SELECT DATEADD(second, MySecondsColumn,
-- Use date math to set the GETDATE time to 0
DATEADD(day, DATEDIFF(day,0,GETDATE()),0));

Whatever the date is, you can get the time by using the appropriate CONVERT
parameters. For example:

SELECT CONVERT(VARCHAR(12), DATEADD(second, MySecondsColumn,0),114)

RLF




"Stan" <Stan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5F817525-6F8B-4216-B3E5-EFAEA070F6E7@xxxxxxxxxxxxxxxx
We have a SQL 2005 database that has the time stored as an integer. I'm
assuming it is the seconds after midnight. Is there a function to convert
this to a readable time?
Thanks
--
Stan Gosselin


.

Relevant Pages

  • Re: Searching on DATETIME Fields
    ... Tibor Karaszi, SQL Server MVP ... > dd/mm/yyyy hh:mm:ss as the search criteria, ... > type to a datetime data type resulted in an out-of-range datetime value. ... > correct format for the data stored in the field it should work? ...
    (microsoft.public.sqlserver.server)
  • Re: Searching on DATETIME Fields
    ... So First try quoting the string, then ensure that the format of the date ... Wayne Snyder, MCDBA, SQL Server MVP ... >>> The field I am querying is of DATETIME data type, ...
    (microsoft.public.sqlserver.server)
  • Re: INSERT syntax error OleDB
    ... > The conversion of a char data type to a datetime data type resulted in an ... about updating dates on an SQL server, with another project I found that I ... had to write to SQL using US format, and SQL would then proceed to save it ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Searching on DATETIME Fields
    ... Because you are using an ambiguous date format. ... Please use a standard format like YYYYMMDD, so SQL Server ... >>> The field I am querying is of DATETIME data type, ...
    (microsoft.public.sqlserver.server)