34

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

1

11 Answers 11

20

The best way to convert a non-current UTC date into local time, prior to SQL Server 2016, is to use the Microsoft .Net Common Language Runtime, or 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 prior to SQL Server 2016 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.

2
  • 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. Mar 10 '15 at 16:57
  • 1
    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.
    – Frédéric
    Apr 1 '16 at 19:18
17

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.

0
13

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)

-- or if you're concerned about non-whole-hour offsets, use:

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @utc).

It is very simple and it does the job.

3
  • 4
    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.
    – Matt
    Oct 17 '17 at 13:18
  • 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)
    – Eamon
    Aug 8 '18 at 1:36
  • @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) ...
    – Quandary
    Mar 1 '19 at 9:38
12

For SQL Server 2016+, you can use AT TIME ZONE. It will automatically handle the day light saving times.

2
11

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.

CREATE FUNCTION [dbo].[UDTToLocalTime](@UDT AS DATETIME)  
RETURNS DATETIME
AS
BEGIN 
--====================================================
--Set the Timezone Offset (NOT During DST [Daylight Saving Time])
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = -5

--====================================================
--Figure out the Offset Datetime
--====================================================
DECLARE @LocalDate AS DATETIME
SET @LocalDate = DATEADD(hh, @Offset, @UDT)

--====================================================
--Figure out the DST Offset for the UDT Datetime
--====================================================
DECLARE @DaylightSavingOffset AS SMALLINT
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--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)
END



GO
0
4

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 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?

0
3

You can easily do this with a CLR Stored Procedure.

[SqlFunction]
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
(
    TimeZoneId NVARCHAR(32) NOT NULL CONSTRAINT PK_TimeZones PRIMARY KEY,
    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
{
    [SqlProcedure]
    public static void PopulateTimezones()
    {
        using (var sql = new SqlConnection("Context Connection=True"))
        {
            sql.Open();

            using (var cmd = sql.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM TimeZones";
                cmd.ExecuteNonQuery();

                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;

                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
1
  • 1
    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.
    – Frédéric
    Apr 1 '16 at 19:20
3

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 http://msdn.microsoft.com/ms725481
 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.

2

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:
    DECLARE
    @App_Date datetime;
    set @App_Date = '20250704 09:00:00'
    
    -- Outputting the local datetime in the UK, allowing for daylight saving:
    SELECT
    case
    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 
    end
    
0
1
DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName', @TimeZone OUT
SELECT @TimeZone
DECLARE @someUtcTime DATETIME
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)
2
  • 2
    Hi Joost! Thanks for posting. If you add some explanation to your answer, it may prove to be a lot easier to understand. Jun 25 '18 at 13:51
  • This does not work in SQL Server 2005 as requested, in fact I don't believe AT TIME ZONE is available until SQL Server 2016. I believe in SQL Server 2016 you wouldn't want to calculate the offset and use dateadd(), just use AT TIME ZONE directly.
    – LeBleu
    Jun 26 '20 at 15:21
1

Based on Colinp_1 post I created a solution to convert a datetime to datetimeoffset that takes DST and TZ into account. Hope this helps!

DECLARE @offset int -- offset in min
DECLARE @dst bit
DECLARE @appDate datetime

set @dst = 1
set @offset = +60
set @appDate = '2017-04-06 14:21:10.000'

-- output the start and end datetime of DST to the given @appDate
select dateadd(hour, 2, 
                  dateadd(day, 1 - datepart(weekday
                                 , dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @appDate), 0))))
                                 , dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @appDate), 0))))) AS 'MEZ -> MESZ'
     , dateadd(hour, 2, 
                  dateadd(day, 1 - datepart(weekday
                                 , dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @appDate), 0))))
                                 , dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @appDate), 0))))) AS 'MESZ -> MEZ'

-- output the @appDate as datetimeoffset including offset and DST
SELECT @dst AS 'DST on'
     , @offset AS 'TZ offset'
     , @appDate AS 'originalDate'
     , qDT.isAppDateInDST
     , qDT.datetimeoffset
     , CONVERT(datetime, qDT.datetimeoffset, 1) AS 'UTC'
FROM (
    SELECT 
        CASE WHEN @dst = 1 THEN -- check if DST is needed
           CASE
                WHEN qDST.isAppDateInDST = 1
                THEN TODATETIMEOFFSET(@appDate, @offset + 60) -- add 1 hour to @appDate when its in DST and convert to DATETIMEOFFSET
                ELSE TODATETIMEOFFSET(@appDate, @offset) -- convert to     DATETIMEOFFSET with given offset
        END
    ELSE 
        TODATETIMEOFFSET(@appDate, @offset) -- convert to DATETIMEOFFSET with given offset
        END AS 'datetimeoffset'
      , qDST.isAppDateInDST
    FROM (
        SELECT 
            CASE WHEN @appDate >= dateadd(hour, 2, 
                                    dateadd(day, 1 - datepart(weekday
                                                    , dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @appDate), 0))))
                                                    , dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @appDate), 0)))))
                    and @appDate < dateadd(hour, 2, 
                                    dateadd(day, 1 - datepart(weekday
                                                    , dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @appDate), 0))))
                                                    , dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @appDate), 0)))))
                THEN 1
            ELSE 0
            END AS 'isAppDateInDST'
    ) qDST
) qDT

GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.