none
Converting UTC datetime values to local time zones RRS feed

  • 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

Answers

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 noibaf Wednesday, November 13, 2013 6:30 PM
    • Unproposed as answer by noibaf Wednesday, November 13, 2013 6:30 PM
    Wednesday, May 21, 2008 2:56 PM