x

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

chandru gravatar image

chandru
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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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 = (DATEDIFF(HOUR, GETDATE(), GETUTCDATE()))
SELECT  @datediff

UPDATE dbo.MyTable
SET DateColumn = DATEADD(hh, @datediff, DateColumn)
more ▼

answered Feb 10, 2011 at 01:44 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.

DATEDIFF(mi,GETDATE(),GETUTCDATE()

DATEADD(mi,@datediff,DateColumn)
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x305
x28
x21

asked: Feb 10, 2011 at 01:30 AM

Seen: 3272 times

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