Convert SQL Server date to GMT + 5.30 in SQL Server 2008/2005

Today one of my subordinate asked me one question about how can we convert GETDATE() to Indian time as our server is hosted in USA. Question is very simple and bit difficult for novice that is why I thought to put it on blog so everybody else can get benefit if they doesn’t know.
Well rather than GETDATE(), I would prefer UTC datetime. Just find the different between your time zone and UTC timezone and add that time to UTC time and you will get your preferred time of your zone.
First I run following T-SQL in our development server in India to find exact time difference between UTC time and Indian time zone.
SELECT DATEDIFF(minute, GetUTCdate(), GetDate());


It will give you 330 minutes, add this 330 minutes to UTC time and you will get Indian time. If already know hours different between your timezone and UTC time, you directly convert it to minutes, don’t need to run above T-SQL.
Now Add 330 minutes to your UTC time with below T-SQL and you are done.
SELECT DATEadd(minute, 330,GetUTCdate()) as indianTime, GETDATE() as serverTime;


Quite an easy task!!! Isn’t it???
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 ofhttp://www.SQLHub.com
Advertisements

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