I would like to convert local time into UTC. I have 2 servers replicated with different time zone. I tried using DATEADD(mi, DATEDIFF(Hour, GETDATE(),GETUTCDATE()), localtime). Still I did not get proper output. Pls let me know.-- Chandru
asked Feb 10 '11 at 01:30 AM in Default
The best way to do this is using a CLR function. Sure, you can add the UTC offset to a date/time - but what about daylight savings times? How would you treat ambiguous times (for example, in the UK, when the clocks go back, there are two 1:00:00AM times that represent two different UTC times).The class you want to look at is called TimeZoneInfo.
answered Feb 10 '11 at 04:57 AM
Matt Whitfield ♦♦
You would be better off storing the data in UTC from the start when entering into localtime.
You made a mistkae in the DATEADD funciton. You should add hours and not minutes.
Look at the following:
Now it is just an exercise in taking the last column in my example and making it into an update.
Pro Tip: You can calculate the DATEDIFF once and store that in a variable and run the update on the whole table:
SET DateColumn = DATEADD(hh, @datediff, DateColumn)