Here’s how to convert a DateTime column record with UTC time
-- SAMPLE DATA
DECLARE @TT TABLE (EID int, CheckIn DATETIME)
INSERT INTO @TT VALUES (2, 'April 02, 2010 11:25am')
INSERT INTO @TT VALUES (4, 'April 03, 2010 9:55am')
INSERT INTO @TT VALUES (5, 'April 07, 2010 11:24am')
INSERT INTO @TT VALUES (2, 'April 10, 2010 11:22am')
INSERT INTO @TT VALUES (3, 'April 15, 2010 5:27am')
-- QUERY
UPDATE @TT
SET CheckIn = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), CheckIn)
SELECT * FROM @TT
Before
After
You can also check my post UTC or GMT time in SQL Server 2005/2008
This of course does not account for time zones with Daylight Savings Time, which would need to be handled with a subquery to a DST data table to get the UTC offset for the original date.
ReplyDeleteYep, Good point!
ReplyDelete