469,080 Members | 1,710 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,080 developers. It's quick & easy.

Timestamp Conversion

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
Jul 20 '05 #1
1 5314
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
--
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Ben | last post: by
3 posts views Thread by Stormblade | last post: by
1 post views Thread by Chris | last post: by
reply views Thread by Ulrich Wisser | last post: by
1 post views Thread by Joseph Geretz | last post: by
7 posts views Thread by JJ | last post: by
5 posts views Thread by kyosohma | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.