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, 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.
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.
For SQL Server 2016+, you can use AT TIME ZONE. It will automatically handle the day light saving times.
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.
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
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?
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.
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();
}
}
}
}
}
Here is an answer written for a specific UK application and based purely on SELECT.
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
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)
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
