One Way to Convert from UTC Time to Local Time

I had a question from someone about how to convert from UTCTime to local time in T-SQL code. Here is one way to do it:

— Convert a UTC Time to a Local Time
DECLARE @UTCDate datetime
DECLARE @LocalDate datetime
DECLARE @TimeDiff int

— Figure out the time difference between UTC and Local time
SET @UTCDate = GETUTCDATE()
SET @LocalDate = GETDATE()
SET @TimeDiff = DATEDIFF(hh, @UTCDate, @LocalDate)

— Check Results
PRINT @LocalDate
PRINT @UTCDate
PRINT @TimeDiff

— Convert UTC to local time
DECLARE @DateYouWantToConvert datetime
DECLARE @ConvertedLocalTime datetime

SET @DateYouWantToConvert = ‘4/25/2007 18:00’
SET @ConvertedLocalTime = DATEADD(hh, @TimeDiff, @DateYouWantToConvert)

— Check Results
PRINT @ConvertedLocalTime

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

4 Responses to One Way to Convert from UTC Time to Local Time

  1. Marko says:

    Hi!
     
    I was looking for the same functionality. If I am right your code finds out the offset between UTC and local time using current localtime and current UTC. If we are in summer and using daylight saving time the difference is not the same as in the winter. Your function is returning different results during winter than during daylight saving time period.
     
    I have written a small function which is returning the offset for central Europe. It can be easily adapted for other time regions as long as daylight saving time is switched on the last sunday in march and on the last sunday in october. The formulae for calculating last sunday work only until 2099, but that is fine for me.🙂
     
    CREATE FUNCTION [dbo].[getUTCOffset]  (@d datetime)RETURNS intASBEGIN DECLARE @Result int
     DECLARE @spring datetime DECLARE @autumn datetime
     — 31 – (5 * year / 4 + 4) mod 7 — day of the last sunday in march
     select @spring = cast (convert (varchar (4), year (@d)) + \’-03-\’ + convert (varchar (2), 31 – (floor (5 * year (@d) / 4) + 4) % 7) + \’ 02:00:00\’ as datetime)
     — 31 – (5 * year / 4 + 1) mod 7 — day of the last sunday in october select @autumn = cast (convert (varchar (4), year (@d)) + \’-10-\’ + convert (varchar (2), 31 – (floor (5 * year (@d) / 4) + 1) % 7) + \’ 02:00:00\’ as datetime)
      if @d between @spring and @autumn    select @Result = -2  else    select @Result = -1
     
      RETURN @ResultEND

  2. Ben Levenson says:

    If you just want to change it to the system local time, a simple, inquery route it to use this:
    SELECT DATEADD(minute, DATEDIFF(minute,getutcdate(),getdate()), UTC_Time_Value_You_Want_In_Local)

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