Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 10 down vote accepted

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.

share|improve this answer
    
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. – srutzky 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 at 19:18

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.

share|improve this answer

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
share|improve this answer

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.

share|improve this answer
1  
There are timezones which are not a full hour offset from UTC so using this DATEPART may cause you problems. – Michael Green Nov 20 '14 at 10:16
2  
Regarding Michael Green's comment, you can address the issue by changing it to SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @utc). – Registered User Feb 17 '15 at 20:26
    
+1 I used a modified version of this to solve a problem. This is exactly what I needed for my scenario. – Registered User Feb 17 '15 at 20:33

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?

share|improve this answer
    
+1 But, consider adding a link for that Wikipedia reference. The final question should probably be a comment on the original question rather than part of this answer. – Paul White Dec 1 '12 at 5:23

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.

share|improve this answer

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();
                }
            }
        }
    }
}
share|improve this answer
    
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 at 19:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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