Back n Forth - DateTime Conversion to UTC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Back n Forth - DateTime Conversion to UTC

Author

Topic

sqlyukon
Starting Member

8 Posts

Posted - 2006-06-27 : 14:56:26

Date Conversion using UTC

--This Works using Seconds

--Be aware that 32-bit signed integer overflows on dates past January 2038.
--Original UTC Time as DateTime for comparison after restore
Declare @OrigUTCTime DateTime
Select @OrigUTCTime = GetUTCDate()
Select @OrigUTCTime as 'Original UTC DateTime'

--Original UTC Time in Seconds
Declare @UTCTimeinSeconds int
Select @UTCTimeinSeconds = DateDiff(ss,{ ts '1970-01-01 00:00:00' }, GETUTCDATE())
Select @UTCTimeinSeconds as 'Original UTCTime in Seconds'

--Restored UTC Time in Seconds to UTC DateTime
Select DateAdd(ss, @UTCTimeinSeconds, { ts '1970-01-01 00:00:00' }) as 'Restored UTCTime from Seconds'

--Milliseconds instead of seconds doesn't work

--Original UTC Time as DateTime
Declare @OrigUTCTime DateTime
Select @OrigUTCTime = GetUTCDate()
Select @OrigUTCTime as 'Original UTC DateTime'

--Original UTC Time in MilliSeconds
Declare @UTCTimeinMilliSeconds Numeric
Select @UTCTimeinMilliSeconds = DateDiff(ms,{ ts '1970-01-01 00:00:00' }, GETUTCDATE())
Select @UTCTimeinMilliSeconds as 'Original UTCTime in MilliSeconds'

--Restored UTC Time in MilliSeconds to UTC DateTime
Select DateAdd(ms, @UTCTimeinMilliSeconds, { ts '1970-01-01 00:00:00' }) as 'Restored UTCTime from MilliSeconds'

My Assumption is its exceeding the limit for the output.

Problem is even the first batch of queries will fail once we reach date Jan 2038.

Does anyone have a solution ?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 16:00:11

Try to calculate milliseconds of today and add that to the number of seconds elapsed since 1970-01-01 multiplied with 1000.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-27 : 16:50:29

What exactly are you trying to do? It looks like you are trying to convert the UTC datetime to UNIX time.

If that is what you are trying to do, you should look at this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources