How can I get the correct offset between UTC and local times for a date that is before or after DST?

  • I currently use the following to get a local datetime from a UTC datetime:

    SET @offset = DateDiff(minute, GetUTCDate(), GetDate())
    SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)

    My problem is that if daylight savings time occurs between GetUTCDate() and @utcDateTime, the @localDateTime ends up being an hour off.

    Is there an easy way to convert from utc to local time for a date that is not the current date?

    I'm using SQL Server 2005

  • The best way to convert a non-current UTC date into local time is to use the CLR. The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary...

    For one of the many examples, check out Harsh Chawla's blog post on the topic.

    Unfortunately, there is nothing built-in that can handle this type of conversion, save for CLR-based solutions. You could write a T-SQL function which does something like this, but then you'd have to implement the date-change logic yourself, and I'd call that decidedly not easy.

    Given the actual complexity of regional variations over time, saying it is "decidedly not easy" to attempt this in pure T-SQL is probably understating it ;-). So yes, SQLCLR is the only reliable and efficient means of performing this operation. +1 for that. FYI: the linked blog post is functionally correct but does not follow best practices so is unfortunately inefficient. Functions for converting between UTC and server local time are available in the SQL# library (which I am the author of), but _not_ in the Free version.

    CLR gets evil when it must be added `WITH PERMISSION_SET = UNSAFE`. Some environments do not allow it like AWS RDS. And it is, well, unsafe. Unfortunately, there is no .Net time zone complete implementation which is usable without `unsafe` permission. See here and here.

  • I have developed and published the T-SQL Toolbox project on codeplex to help anybody who struggles with datetime and timezone handling in Microsoft SQL Server. It’s open source and completely free to use.

    It offers easy datetime conversion UDFs using plain T-SQL (no CLRs) in addition with pre-filled configuration tables out of the box. And it has full DST (daylight saving time) support.

    A list of all supported timezones can be found in table "DateTimeUtil.Timezone" (provided within the T-SQL Toolbox database).

    In your example, you can use the following sample:

    SELECT [DateTimeUtil].[UDF_ConvertUtcToLocalByTimezoneIdentifier] (
        'W. Europe Standard Time', -- the target local timezone
        '2014-03-30 00:55:00' -- the original UTC datetime you want to convert

    This will return the converted local datetime value.

    Unfortunately, it is supported for SQL Server 2008 or later only because of newer data types (DATE, TIME, DATETIME2). But as the full source code is provided you can easily adjust the tables and UDFs by replacing them by DATETIME. I don't have a MSSQL 2005 available for testing, but it should work with MSSQL 2005, too, then. In case of questions, just let me know.

  • I always use this TSQL command.

    -- the utc value 
    declare @utc datetime = '20/11/2014 05:14'
    -- the local time
    select DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate()), @utc)

    It is very simple and it does the job.

    There are timezones which are not a full hour offset from UTC so using this DATEPART may cause you problems.

    Regarding Michael Green's comment, you can address the issue by changing it to SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @utc).

    This doesn't work as you are only determining if the current time is DST or not, then comparing a time that could be DST or not. Using your above example code and datetime in the UK currently tells me that it should be 6:14am, however November is outside DST so it should be 5:14am as GMT and UTC coincide.

    Whilst I aggree this doesn't address the actual question, as far as this answer is concerned I think the following is better: SELECT DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), @utc)

    @Ludo Bernaerts: First use milliseconds, second: this does not work because the UTC offset today might be different than the UTC-offset at a certain time (daylight-saving - summer vs winter time) ...

  • I found this answer on StackOverflow that provides a User Defined Function that appears to accurately translate the datetimes

    The only thing you need to modify is the @offset variable at the top to set it to the Timezone offset of the SQL server running this function. In my case, our SQL server uses EST, which is GMT - 5

    It's not perfect and probably won't work for many cases such has half-hour or 15-minute TZ offsets (for those I'd recommend a CLR function like Kevin recommended), however it works well enough for most generic time zones in North America.

    --Set the Timezone Offset (NOT During DST [Daylight Saving Time])
    SET @Offset = -5
    --Figure out the Offset Datetime
    SET @LocalDate = DATEADD(hh, @Offset, @UDT)
    --Figure out the DST Offset for the UDT Datetime
    DECLARE @DaylightSavingOffset AS SMALLINT
    --Get Year
    SET @Year = YEAR(@LocalDate)
    --Get First Possible DST StartDay
    IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
    ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
    --Get DST StartDate 
    WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)
    --Get First Possible DST EndDate
    IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'
    ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'
    --Get DST EndDate 
    WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)
    --Get DaylightSavingOffset
    SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END
    --Finally add the DST Offset 
    RETURN DATEADD(hh, @DaylightSavingOffset, @LocalDate)
  • For SQL Server 2016+, you can use AT TIME ZONE. It will automatically handle the day light saving times.

    This should be the accepted answer.

  • There are a couple of good answers to a similar question asked on Stack Overflow. I wound up using a T-SQL approach from the second answer by Bob Albright to clean up a mess caused by a data conversion consultant.

    It worked for almost all of our data, but then I later realized that his algorithm only works for dates as far back as April 5, 1987, and we had some dates from the 1940s that still didn't convert properly. We ultimately needed the UTC dates in our SQL Server database to line up with an algorithm in a 3rd party program that used the Java API to convert from UTC to local time.

    I like the CLR example in Kevin Feasel's answer above using Harsh Chawla's example, and I'd also like to compare it to a solution that uses Java, since our front end uses Java to do the UTC to local time conversion.

    Wikipedia mentions 8 different constitutional amendments that involve time zone adjustments prior to 1987, and many of those are very localized to different states, so there is a chance that the CLR and Java may interpret them differently. Does your front-end application code use dotnet or Java, or are dates before 1987 an issue for you?

  • You can easily do this with a CLR Stored Procedure.

    public static SqlDateTime ToLocalTime(SqlDateTime UtcTime, SqlString TimeZoneId)
        if (UtcTime.IsNull)
            return UtcTime;
        var timeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneId.Value);
        var localTime = TimeZoneInfo.ConvertTimeFromUtc(UtcTime.Value, timeZone);
        return new SqlDateTime(localTime);

    You can store the available TimeZones in a table:

    CREATE TABLE TimeZones
        DisplayName NVARCHAR(64) NOT NULL,
        SupportsDaylightSavingTime BIT NOT NULL,

    And this stored procedure will fill the table with the possible time zones on your server.

    public partial class StoredProcedures
        public static void PopulateTimezones()
            using (var sql = new SqlConnection("Context Connection=True"))
                using (var cmd = sql.CreateCommand())
                    cmd.CommandText = "DELETE FROM TimeZones";
                    cmd.CommandText = "INSERT INTO [dbo].[TimeZones]([TimeZoneId], [DisplayName], [SupportsDaylightSavingTime]) VALUES(@TimeZoneId, @DisplayName, @SupportsDaylightSavingTime);";
                    var Id = cmd.Parameters.Add("@TimeZoneId", SqlDbType.NVarChar);
                    var DisplayName = cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar);
                    var SupportsDaylightSavingTime = cmd.Parameters.Add("@SupportsDaylightSavingTime", SqlDbType.Bit);
                    foreach (var zone in TimeZoneInfo.GetSystemTimeZones())
                        Id.Value = zone.Id;
                        DisplayName.Value = zone.DisplayName;
                        SupportsDaylightSavingTime.Value = zone.SupportsDaylightSavingTime;

    CLR gets evil when it must be added `WITH PERMISSION_SET = UNSAFE`. Some environments do not allow it like AWS RDS. And it is, well, unsafe. Unfortunately, there is no .Net time zone complete implementation which is usable without `unsafe` permission. See here and here.

  • SQL Server version 2016 will solve this issue once and for all. For earlier versions a CLR solution is probably easiest. Or for a specific DST rule (like US only), a T-SQL function can be relatively simple.

    However, I think a generic T-SQL solution might be possible. As long as xp_regread works, try this:

    CREATE TABLE #tztable (Value varchar(50), Data binary(56));
    DECLARE @tzname varchar(150) = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TimeZoneKeyName', @tzname OUT;
    SELECT @tzname = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\' + @tzname
    INSERT INTO #tztable
    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TZI';
    SELECT                                                                                  -- See
     CAST(CAST(REVERSE(SUBSTRING(Data,  1, 4)) AS binary(4))      AS int) AS BiasMinutes,   -- UTC = local + bias: > 0 in US, < 0 in Europe!
     CAST(CAST(REVERSE(SUBSTRING(Data,  5, 4)) AS binary(4))      AS int) AS ExtraBias_Std, --   0 for most timezones
     CAST(CAST(REVERSE(SUBSTRING(Data,  9, 4)) AS binary(4))      AS int) AS ExtraBias_DST, -- -60 for most timezones: DST makes UTC 1 hour earlier
     -- When DST ends:
     CAST(CAST(REVERSE(SUBSTRING(Data, 13, 2)) AS binary(2)) AS smallint) AS StdYear,       -- 0 = yearly (else once)
     CAST(CAST(REVERSE(SUBSTRING(Data, 15, 2)) AS binary(2)) AS smallint) AS StdMonth,      -- 0 = no DST
     CAST(CAST(REVERSE(SUBSTRING(Data, 17, 2)) AS binary(2)) AS smallint) AS StdDayOfWeek,  -- 0 = Sunday to 6 = Saturday
     CAST(CAST(REVERSE(SUBSTRING(Data, 19, 2)) AS binary(2)) AS smallint) AS StdWeek,       -- 1 to 4, or 5 = last <DayOfWeek> of <Month>
     CAST(CAST(REVERSE(SUBSTRING(Data, 21, 2)) AS binary(2)) AS smallint) AS StdHour,       -- Local time
     CAST(CAST(REVERSE(SUBSTRING(Data, 23, 2)) AS binary(2)) AS smallint) AS StdMinute,
     CAST(CAST(REVERSE(SUBSTRING(Data, 25, 2)) AS binary(2)) AS smallint) AS StdSecond,
     CAST(CAST(REVERSE(SUBSTRING(Data, 27, 2)) AS binary(2)) AS smallint) AS StdMillisec,
     -- When DST starts:
     CAST(CAST(REVERSE(SUBSTRING(Data, 29, 2)) AS binary(2)) AS smallint) AS DSTYear,       -- See above
     CAST(CAST(REVERSE(SUBSTRING(Data, 31, 2)) AS binary(2)) AS smallint) AS DSTMonth,
     CAST(CAST(REVERSE(SUBSTRING(Data, 33, 2)) AS binary(2)) AS smallint) AS DSTDayOfWeek,
     CAST(CAST(REVERSE(SUBSTRING(Data, 35, 2)) AS binary(2)) AS smallint) AS DSTWeek,
     CAST(CAST(REVERSE(SUBSTRING(Data, 37, 2)) AS binary(2)) AS smallint) AS DSTHour,
     CAST(CAST(REVERSE(SUBSTRING(Data, 39, 2)) AS binary(2)) AS smallint) AS DSTMinute,
     CAST(CAST(REVERSE(SUBSTRING(Data, 41, 2)) AS binary(2)) AS smallint) AS DSTSecond,
     CAST(CAST(REVERSE(SUBSTRING(Data, 43, 2)) AS binary(2)) AS smallint) AS DSTMillisec
    FROM #tztable;
    DROP TABLE #tztable

    A (complex) T-SQL function could use this data to determine the exact offset for all dates during the current DST rule.

  • Here is an answer written for a specific UK application and based purely on SELECT.

    1. No timezone offset (e.g. UK)
    2. Written for daylight saving starting on last Sunday of March and finishing on last Sunday of October (UK rules)
    3. Not applicable between midnight and 1 AM on the day daylight saving starts. This could be corrected but the application it was written for does not require it.

      -- A variable holding an example UTC datetime in the UK, try some different values:
      @App_Date datetime;
      set @App_Date = '20250704 09:00:00'
      -- Outputting the local datetime in the UK, allowing for daylight saving:
      when @App_Date >= dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0))))
          and @App_Date < dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0))))
          then DATEADD(hour, 1, @App_Date) 
      else @App_Date 

    You may want to consider using the long date part names, instead of the short ones. Just for clarity. See Aaron Bertrand's excellent article on several "bad habits"

    Also, welcome to [] - please take the [tour] if you haven't already!

    Thanks all, helpful comments and helpful edit suggestions, I'm a total newbie here, somehow I've managed to accumulate 1 point which is fab :-).

    now you have 11.

  • DECLARE @TimeZone VARCHAR(50)
    EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName', @TimeZone OUT
    SELECT @TimeZone
    SET @someUtcTime = '2017-03-05 15:15:15'
    DECLARE @TimeBiasAtSomeUtcTime INT
    SELECT @TimeBiasAtSomeUtcTime = DATEDIFF(MINUTE, @someUtcTime, @someUtcTime AT TIME ZONE @TimeZone)
    SELECT DATEADD(MINUTE, @TimeBiasAtSomeUtcTime * -1, @someUtcTime)

    Hi Joost! Thanks for posting. If you add some explanation to your answer, it may prove to be a lot easier to understand.

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM