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

more ▼

asked Feb 10, 2011 at 01:30 AM in Default

avatar image

31 2 2 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Feb 10, 2011 at 04:57 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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.

Feb 10, 2011 at 05:10 AM WilliamD

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.

Feb 10, 2011 at 05:42 AM chandru

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();
Feb 10, 2011 at 06:50 AM Matt Whitfield ♦♦

Thank you Matt. I will try the same and let you know.

Feb 11, 2011 at 05:37 AM chandru
(comments are locked)
10|1200 characters needed characters left

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
 UPDATE dbo.MyTable
 SET DateColumn = DATEADD(hh, @datediff, DateColumn)
more ▼

answered Feb 10, 2011 at 01:44 AM

avatar image

26.2k 18 38 48

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.

Feb 10, 2011 at 01:57 AM chandru

Then i suggest you change the DATEDIFF and DATEADD to minutes to reflect that the difference should be considered in that way.



Feb 10, 2011 at 04:13 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 10, 2011 at 01:30 AM

Seen: 4155 times

Last Updated: Feb 10, 2011 at 01:30 AM

Copyright 2018 Redgate Software. Privacy Policy