# Converting UTC datetime values to local time zones

• ### Question

•

I would like to convert a UTC datetime value to any Local Time Zone.  Below is some code I'm trying to ensure works correctly.  Anyone care to comment?

I have used the following lines before the function to obtain the 2nd parameter:

SELECT GETUTCDATE()

SELECT GETDATE()

SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')

DECLARE @UTC DATETIME

EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4

-- =============================================

--  Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.

-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value

-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>

-- =============================================

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)

RETURNS DATETIME AS

BEGIN

IF ( DATEPART(hh, @UTCDate) <> 0 )

BEGIN

DECLARE @LocalDate DATETIME

DECLARE @UTCDelta INT

DECLARE @thisYear INT

DECLARE @DSTDay INT

DECLARE @NormalDay INT

DECLARE @DSTDate DATETIME

DECLARE @NormalDate DATETIME

SET @thisYear = YEAR(@UTCDate)

IF (@thisYear < 2007 )

BEGIN

SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1

SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)

SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

ELSE

BEGIN

SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )

SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))

BEGIN

SET @UTCDelta = @TZ + 1

END

ELSE

BEGIN

SET @UTCDelta = @TZ

END

-- now convert utc date to local date

SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)

END

ELSE

BEGIN

SET @LocalDate = @UTCDate

END

RETURN(@LocalDate)

END

GO

Tuesday, May 20, 2008 7:04 PM

### All replies

•

This is a near-impossible task.  "Local Time" means (as I define it) "The time based on GMT and an adjustment for the geographic location."  Well, you have to know the EXACT geographic location and the the exact time in question.  For example, Arizona does not observe Daylight Saving Time.  So, while it may be Pacific time part of the year, it may also be Mountain time for part of the year.  PST, PDT, MST, MDT.  Which is which and when?

To further complicate, the rules for DST (as well as time zones themselves) are not exactly "Fixed."  Just try to figure out the correct "local time" in Australia.    In the US, each zone (that observes DST) switches at 2:00 AM according to their local time.  in Europe, all zones switch at the same instant, all at once.

And, of course, the rules for DST change from time to time. WE have new rules for DST in 2008, so the conversion is different than it was in 2007 and both are still different than the rules in the year 1960.

Of course, adding/subtracting hours is a piece of cake.

But a true GMT to Local Time conversion....nearly impossible.

My suggestion, if you're in the US, use only GMT, and STANDARD TIME for all times:  EST, CST, MST, PST, Alaska, FM etc. and make no adjustment for Daylight Time.

Tuesday, May 20, 2008 8:11 PM
• Thanks for the input.  Are you saying it is you who wrote this routine above?  Either way - appreciate the feed-back

Tuesday, May 20, 2008 8:21 PM
• I've written a routine or two that tries to do this.  But it's just not really very easily done.

select GetDate()

gives you local system time, based on the settings on your sql server.

select GetUTCDate()

gives you what SQL thinks is UTC, based again on local settings.

If you REALLY need to do this, you'll need two tables:

TimeZone:

TimeZoneID

TimeZoneDescription

DSTBias:

ZoneID

DSTStart_GMT

DSTEnd_GMT

DSTOffsetInMinutes

StandardOffsetInMinutes

You'll populated TimeZone with every unique timezone that you will encounter.  MST is not granular enough. You will need probably two entries for each US timezone:

1.  Eastern (Oberves DST)

2.  Eastern (No DST)

3.  Central (Oberves DST)

4.  Central (No DST)

...

then in DSTBias, you'll have one entry per TimeZoneID per year, showing the start/end date for DST (or NULL if they do not observe) then the GMT time where DST kicks in or out.  Plus you'll need to know the bias.  Don't assume that all times zones are based on hours.  Some time zones are 1.5 hours from GMT (which in this table would be "90" meaning 90 mintues).

Then, in your fuction, you'll join on these two tables, look up the correct year and determine the correct bais to add or subtract from GMT to make it the correct local time.

at least, that's how I've approached this issue in the past.

Tuesday, May 20, 2008 8:42 PM
• I've used CLR functions for this purpose. This project is very handy. You can convert any time between timezones historically using the registry..

http://channel9.msdn.com/ShowPost.aspx?PostID=142586

Tuesday, May 20, 2008 10:01 PM
• Ok, thanks both of you .....

• Proposed as answer by Wednesday, November 13, 2013 6:30 PM
• Unproposed as answer by Wednesday, November 13, 2013 6:30 PM
Wednesday, May 21, 2008 2:56 PM