asked
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)
Hi William, The problem here is, we are using the DB for about 7 or 8 years and it is not possible to get it back or make changes with DB. Second, we have half an hour, 15 minutes difference in GMT. When we use Hours in DATEADD function, will it consider this case also? Because I have tried both Hours and Minutes, but getting difference in result.
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.
Matt, totally valid point(+1). The OP states that they aren't looking to change code/db design just stomp all current data into UTC. Would a CLR function then be used to take the current system's Time Zone Info and calculate what the date/time column should have been according to UTC + DST offset? I have not played around with date/time's over timelines, although our DB should account for this. The design (from before I started) uses GETUTCDATE() to find out data creation/alteration timestamps. DST had never been considered AFAIK, but is not *that* important to us. I would be interested to see how that should be implemented.
I do accept Matt and William. I haven't yet tried using CLR functions in SQL. But when I convert local datetime to UTC using DateDiff and DateAdd I found a maximum of 5 minutes difference between the two servers. And still it costs much to the business. Also as Matt stated I just not aware of day light savings and all. Could you please let me know how to use CLR and day light savings. Thanks in advance.
Some example code might be as follows: CultureInfo ci = new CultureInfo("en-GB"); DateTime convertedDate = DateTime.SpecifyKind(DateTime.Now,
DateTimeKind.Local); DateTime utcDate = convertedDate.ToUniversalTime();
