Timestamp Conversion

[Dave]

I am working with an application that is returning a second based
timestamp. It returns values based on the 86440 second day in GMT.

Is there an ability within SQL Server to convert the value into an
hour time value?

I need to take this value and convert it to a hour value for the time
zone the client is in.

Thanks,

Dave

[David Portas]

Do you know exactly what your seconds-based timestamp represents? It sounds
like a count of the number of seconds since some base date. Unix systems for
example use timestamps based on a count of the number of seconds since
midnight on 1970-01-01. This can be converted to a DATETIME as follows:

SELECT DATEADD(SECOND,@ts_seconds,'19700101')

If you have a known date and time for one of your numeric timestamps it
shouldn't be too difficult to work out what the base date is. Just turn the
above function around. For example if you have a timestamp value that you
know is for 17:59 today, do this:

SELECT DATEADD(SECOND,-@ts_seconds,'2004-09-22T17:59:00') AS base_date

Once you have the base date you can easily convert any timestamp value.

--
David Portas
SQL Server MVP
--