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(HOURGETDATE()GETUTCDATE()) SELECT DATEADD(HH @DIFF GETDATE())