How to convert local time to UTC time in SQL

Question

Please help to find how we can convert Local time to UTC time in SQL..?

Hemant Srivastava · Answer

For your First question, if you change hour to minute or second or any date part, SQL sever puts your Local Time-UTC Difference in that unit. It means, suppose difference is 5 hours between your local time and UTC time and you use 'Seconds'segment here instead of 'Hour', SQL server converts that time difference into seconds (i.e 18000 seconds). Even if you use 'DATE' part, SQL server will give the same UTC time. For your second question, it takes care your date, month year change it's because you are using Built-in SQL method 'DATEADD()' that is supposed to handle all of these date change issues. DECLARE @DIFF INT SET @DIFF = DATEDIFF ( HOUR , GETDATE (), GETUTCDATE ()) SELECT @DIFF AS DIFFERENCE_IN_HOUR SELECT DATEADD ( HOUR , @DIFF , GETDATE ()) AS CURRENT_UTC_TIME ------------------------------------------------------------ DECLARE @DIFF1 INT SET @DIFF1 = DATEDIFF ( MINUTE , GETDATE (), GETUTCDATE ()) SELECT @DIFF1 AS DIFFERENCE_IN_MINUTE SELECT DATEADD ( MINUTE , @DIFF1 , GETDATE ()) AS CURRENT_UTC_TIME ------------------------------------------------------------- DECLARE @DIFF2 INT SET @DIFF2 = DATEDIFF ( SECOND , GETDATE (), GETUTCDATE ()) SELECT @DIFF2 AS DIFFERENCE_IN_SECONDS SELECT DATEADD ( SECOND , @DIFF2 , GETDATE ()) AS CURRENT_UTC_TIME ------------------------------------------------------------ DECLARE @DIFF3 INT SET @DIFF3 = DATEDIFF ( DAY , GETDATE (), GETUTCDATE ()) SELECT @DIFF3 AS DIFFERENCE_IN_DAYS SELECT DATEADD ( DAY , @DIFF3 , GETDATE ()) AS CURRENT_UTC_TIME ----------------------------------------------------------- you may look the sql query result:

Ann Asejo · Answer

Hi! I want to try the script attached. But the table for time_zones is not provided. May I ask if you could provide me this table? Thank you!

Hemant Srivastava · Answer

It's good, it worked for you.. pls mark this thread accepted for other's reference.

Tulika Kumar · Answer

Thanks for your explanation. It solved my problem..

Tulika Kumar · Answer

Thanks Deepesh but I don't want to use function for this small conversion. Thanks Hemant, you solution is working fine but I have doubts... like: 1. Why are you considering only HOUR part.. what about other minutes and seconds? 2. At that time, when date is changed after UTC conversion, will it work?

Jignesh Trivedi · Answer

hi, check below link it might help inyou problem http://stackoverflow.com/questions/6064674/sql-server-2008-how-to-convert-gmtutc-datetime-to-local-datetime

Deepesh · Answer

Hi, First create the function attached in the file. Parameters are local time and your timezone. Then call it like below: DECLARE @DT DATETIME = GETDATE() SELECT @DT AS LOCAL_TIME, dbo.GET_UTCTIME(@DT, 'CT') AS UTC_TIME Hope this helps and solves your problem.

Hemant Srivastava · Answer

Try the following one, hope it should work... DECLARE @DIFF INT SET @DIFF = DATEDIFF(HOUR,GETDATE(),GETUTCDATE()) SELECT DATEADD(HH, @DIFF, GETDATE())