Convert local server time to UTC time in replicated servers
Hi All, 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
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: DECLARE @date datetime, @utcdate datetime SELECT @date = GETDATE(), @utcdate = GETUTCDATE() SELECT DATEDIFF(HOUR, @date, @utcdate), -- difference in hours @date, --current date @utcdate, -- what current date should be DATEADD(hh, (DATEDIFF(HOUR, @date, @utcdate)), @date) --what @date will be 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: DECLARE @datediff smallint SELECT @datediff = (DATEDIFF(HOUR, GETDATE(), GETUTCDATE())) SELECT @datediff UPDATE dbo.MyTable SET DateColumn = DATEADD(hh, @datediff, DateColumn)
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.