June 27, 2006 at 11:44 am
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.
Ashraf
June 28, 2006 at 12:12 pm
Note that DATEDIFF and DATEADD return INT, not BIGINT; that may well be the limitation you're running into.
Couldn't you just take your "seconds" result, multiply by 1000, and assign it to a BIGINT value?
Hope this helps..
- Ward Pond
blogs.technet.com/wardpond
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply