I would like to contribute my codes related to the date conversion between local time and UTC time for both current and historical dates, including day light savings time consideration. I reviewed some recent postings but could not find the solution I am looking for so I summarize the solutions I found to develop my own solution. I am running my codes in SQL Server 2008R2. Test cases are provided as comments before each function. Feel free to check out the Credits section for related postings, try out my codes, and let me know if I miss anything. Thanks.
-- Test 1: select dbo.fn_isDayLightSavings(CAST('2014-01-01' AS DATE)), expect 0
-- Test 2: select dbo.fn_isDayLightSavings(CAST('2014-04-14' AS DATE)), expect 1
IF OBJECT_ID(N'fn_isDayLightSavings') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID(N'fn_isDayLightSavings'),'IsScalarFunction') = 1
DROP FUNCTION fn_isDayLightSavings;
GO
CREATE FUNCTION fn_isDayLightSavings (@dt AS DATETIME)
RETURNS TINYINT
AS
BEGIN
DECLARE @rtn TINYINT, @year INT, @dtsStartMonth DATETIME, @dtsEndMonth DATETIME, @dstStart DATETIME, @dstEnd DATETIME;
SET @year = DATEPART(YEAR, @dt);
-- In year 2007, US day light savings period changes from Apr-Oct to Mar-Nov.
if @year < 2007
BEGIN
-- Last Sunday of April at 2 AM
SET @dtsStartMonth = DATEADD(MONTH, 4, DATEADD(YEAR, @year - 1900, 0));
SET @dstStart = DATEADD(HOUR, 2, DATEADD(day, -(DATEPART(dw, @dtsStartMonth) - 1), @dtsStartMonth));
-- Last Sunday of October at 2 AM
SET @dtsEndMonth = DATEADD(MONTH, 10, DATEADD(YEAR, @year - 1900, 0));
SET @dstEnd = DATEADD(HOUR, 2, DATEADD(day, -(DATEPART(dw, @dtsEndMonth) - 1), @dtsEndMonth));
END
else
BEGIN
-- 2nd Sunday of March at 2 AM
SET @dtsStartMonth = DATEADD(MONTH, 2, DATEADD(YEAR, @year - 1900, 0));
SET @dstStart = DATEADD(HOUR, 2, DATEADD(day, ((15 - DATEPART(dw, @dtsStartMonth)) % 7) + 7, @dtsStartMonth));
-- 1st Sunday of November at 2 AM
SET @dtsEndMonth = DATEADD(MONTH, 10, DATEADD(YEAR, @year - 1900, 0));
SET @dstEnd = DATEADD(HOUR, 2, DATEADD(day, ((8 - DATEPART(dw, @dtsEndMonth)) % 7) + 7, @dtsEndMonth));
END
if @dt BETWEEN @dstStart AND @dstEnd SET @rtn=1 ELSE SET @rtn=0;
RETURN @rtn;
END
GO
-- Test 1: select dbo.fn_DateTime2UTC(CAST('2014-01-01 01:00:00' AS DATETIME), 0), expect '2014-01-01 07:00:00.000'
-- Test 2: select dbo.fn_DateTime2UTC(CAST('2014-01-01 01:00:00' AS DATETIME), 1), expect '2013-01-01 07:00:00.000'
-- Test 3: select dbo.fn_DateTime2UTC(CAST('2014-05-01 01:00:00' AS DATETIME), 0), expect '2014-05-01 06:00:00.000'
-- Test 4: select dbo.fn_DateTime2UTC(CAST('2014-05-01 01:00:00' AS DATETIME), 1), expect '2014-05-01 07:00:00.000'
IF OBJECT_ID(N'fn_DateTime2UTC') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID(N'fn_DateTime2UTC'),'IsScalarFunction') = 1
DROP FUNCTION fn_DateTime2UTC;
GO
CREATE FUNCTION fn_DateTime2UTC (@dt AS DATETIME, @ignoreDST AS TINYINT = 0)
-- do CAST(? AS DATETIMEOFFSET), if need datetimeoffset type
RETURNS DATETIME
AS
BEGIN
DECLARE @tzOffset INT, @utcDt DATETIME;
-- Get current time zone offset in minutes
SET @tzOffset = DATEPART(TZoffset, SYSDATETIMEOFFSET()) +
(CASE WHEN dbo.fn_isDayLightSavings(@dt)=1 THEN 60 ELSE 0 END);
if dbo.fn_isDayLightSavings(@dt)=0
set @utcDt = DATEADD(MINUTE, -@tzOffset, @dt);
else if @ignoreDST=0
set @utcDt = DATEADD(MINUTE, -@tzOffset, @dt);
else
set @utcDt = DATEADD(MINUTE, -@tzOffset+60, @dt);
return @utcDt;
END
GO
-- Test 1: select dbo.fn_UTC2DateTime(CAST('2014-01-01 07:00:00.000' AS DATETIME), 0), expect '2014-01-01 01:00:00'
-- Test 2: select dbo.fn_UTC2DateTime(CAST('2013-01-01 07:00:00.000' AS DATETIME), 1), expect '2014-01-01 01:00:00'
-- Test 3: select dbo.fn_UTC2DateTime(CAST('2014-05-01 06:00:00.000' AS DATETIME), 0), expect '2014-05-01 01:00:00'
-- Test 4: select dbo.fn_UTC2DateTime(CAST('2014-05-01 07:00:00.000' AS DATETIME), 1), expect '2014-05-01 01:00:00'
IF OBJECT_ID(N'fn_UTC2DateTime') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID(N'fn_UTC2DateTime'),'IsScalarFunction') = 1
DROP FUNCTION fn_UTC2DateTime;
GO
CREATE FUNCTION fn_UTC2DateTime (@utcDt AS DATETIME, @ignoreDST AS TINYINT = 0)
RETURNS DATETIME
AS
BEGIN
DECLARE @tzOffset INT, @dt DATETIME;
-- Get current time zone offset in minutes
SET @tzOffset = DATEPART(TZoffset, SYSDATETIMEOFFSET()) +
(CASE WHEN dbo.fn_isDayLightSavings(@utcDt)=1 THEN 60 ELSE 0 END);
if dbo.fn_isDayLightSavings(@utcDt)=0
set @dt = DATEADD(MINUTE, @tzOffset, @utcDt);
else if @ignoreDST=0
set @dt = DATEADD(MINUTE, @tzOffset, @utcDt);
else
set @dt = DATEADD(MINUTE, @tzOffset-60, @utcDt);
return @dt;
END
GO
-- Test 1: select dbo.fn_UTC2DateTimeOffset(CAST('2014-01-01 07:00:00.000' AS DATETIME), 0), expect '2014-01-01 01:00:00.0000000 -06:00'
-- Test 2: select dbo.fn_UTC2DateTimeOffset(CAST('2013-01-01 07:00:00.000' AS DATETIME), 1), expect '2014-01-01 01:00:00.0000000 -06:00'
-- Test 3: select dbo.fn_UTC2DateTimeOffset(CAST('2014-05-01 06:00:00.000' AS DATETIME), 0), expect '2014-05-01 01:00:00.0000000 -05:00'
-- Test 4: select dbo.fn_UTC2DateTimeOffset(CAST('2014-05-01 07:00:00.000' AS DATETIME), 1), expect '2014-05-01 00:00:00.0000000 -06:00'
IF OBJECT_ID(N'fn_UTC2DateTimeOffset') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID(N'fn_UTC2DateTimeOffset'),'IsScalarFunction') = 1
DROP FUNCTION fn_UTC2DateTimeOffset;
GO
CREATE FUNCTION fn_UTC2DateTimeOffset (@utcDt AS DATETIME, @ignoreDST TINYINT = 0)
RETURNS DATETIMEOFFSET
AS
BEGIN
DECLARE @tzOffset INT, @dt DATETIMEOFFSET;
-- Get current time zone offset in minutes
SET @tzOffset = DATEPART(TZoffset, SYSDATETIMEOFFSET()) +
(CASE WHEN dbo.fn_isDayLightSavings(@utcDt)=1 THEN 60 ELSE 0 END);
if dbo.fn_isDayLightSavings(@utcDt)=0
set @dt = SWITCHOFFSET(CAST(@utcDt AS DATETIMEOFFSET), @tzOffset);
else if @ignoreDST=0
set @dt = SWITCHOFFSET(CAST(@utcDt AS DATETIMEOFFSET), @tzOffset);
else
set @dt = SWITCHOFFSET(CAST(@utcDt AS DATETIMEOFFSET), @tzOffset-60);
return @dt;
END
GO
-- Credits:
-- Determination of day light savings start and end time, Jamie F., 11/1/2013
-- (http://stackoverflow.com/questions/19732896/how-to-create-daylight-savings-time-start-and-end-function-in-sql-server)
-- Day light savings time calendar, USNO
-- (http://aa.usno.navy.mil/faq/docs/daylight_time.php)
-- Time zone offset, Microsoft
-- (http://msdn.microsoft.com/en-us/library/ms174420.aspx)
-- Local system date time with time zone, Robert Cantor, 12/7/2013
-- (http://stackoverflow.com/questions/1205142/tsql-how-to-convert-local-time-to-utc-sqlserver2008)
-- Convert date with time zone and day light savings, Eric Z Beard, 8/24/2008
-- (http://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005#25073)
