Convert local DateTime to UTC DateTime in SQL Server 200/2005

I have seen one question in many forums about asking how to convert local DateTime to UTC DateTime? This is really very simple task to done.  All you need to do is, get difference between your local datetime and UTC datetime. GetDate() function will you, your local DateTime and for UTC DateTime, you can use getUTCdate() function. Simply find the difference between these two DateTime in Minutes or in Hours and Add/Deduct that Minutes/Hours from your local datetime and you are done. Let us see how easily we can do it

 

select DATEADD(hh,datediff(hh,GETDATE(),getUTCdate()),GETDATE()) as ‘UTC DateTime’, GETDATE() as ‘Local DateTime’

–or you can pass your own dateTime field or variable like this.

declare @dt datetime

set @dt=‘9/22/2009 1:25:00 PM’

select DATEADD(hh,datediff(hh,GETDATE(),getUTCdate()),@dt) as ‘UTC DateTime’

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements

4 Responses to “Convert local DateTime to UTC DateTime in SQL Server 200/2005”

  1. BrandonGalderisi Says:

    In you're code:declare @dt datetimeset @dt='9/22/2009 1:25:00 PM'select DATEADD(hh,datediff(hh,GETDATE(),getUTCdate()),@dt) as 'UTC DateTime'You are using the current offset of GETDATE() and GETDATEUTC() to account for local time shift. What about for locale's that observe daylight savings time. Your calculation will incorrectly calculate the local time for values that are not in the same phase of daylight savings time. Other than that, looks good.BrandonGalderisi

  2. BrandonGalderisi Says:

    In you're code:declare @dt datetimeset @dt='9/22/2009 1:25:00 PM'select DATEADD(hh,datediff(hh,GETDATE(),getUTCdate()),@dt) as 'UTC DateTime'You are using the current offset of GETDATE() and GETDATEUTC() to account for local time shift. What about for locale's that observe daylight savings time. Your calculation will incorrectly calculate the local time for values that are not in the same phase of daylight savings time. Other than that, looks good.BrandonGalderisi

  3. Ritesh Shah Says:

    Hi Brandon,first of all, it is my absolute pleasure that almighty of SQLServerNation and MVP in queue left me comment, thank you very much for that. I will surely work on your very positive comment.

  4. Ritesh Shah Says:

    Hi Brandon,first of all, it is my absolute pleasure that almighty of SQLServerNation and MVP in queue left me comment, thank you very much for that. I will surely work on your very positive comment.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: