After a cursory search, could not find any built-in for SQL Server that do this or anything on the net and had to knock something out very quick. Would be very interested in a more efficient manner to convert to DateTimeOffset in a way that is daylight savings time aware. This bit of code gives me 3 years to find a better solution. Of course would be best to just have all the servers set to UTC the problem solved! 🙂
/*************************************************************************
* NAME:
* dbo.toUSTZ
*
* TYPE:
* Function
*
* DESCRIPTION:
* Converts a US Timezone to DateTimeOffset accounting for Daylight Savings Time
* DST in US begins at 2:00 a.m. on the second Sunday of March and
* ends at 2:00 a.m. on the first Sunday of November
* REF: http://www.nist.gov/pml/div688/dst.cfm
*
* RETURNS:
* datetimeoffset(0) (ie: '2014-10-31 18:25:51 +00:00')
*
* PARAMETERS:
* @thedate datetime2 --the date to be converted
* @timezone char(2) --date's US time zone
* Code ST DST Time Zone
* UT +0 +0 Universal Time
* AT -4 -4 Atlantic
* ET -5 -4 Eastern
* CT -6 -5 Central
* MT -7 -6 Mountain
* AZ -7 -7 Arizona
* PT -8 -7 Pacific
* AK -9 -8 Alaska
* HT -10 -10 Hawaii
* ST -11 -11 Samoa
* CH +10 +10 Chamorro
*
* TODO: Add additional years and/or find more elegant way to do this...
* REF: http://www.nist.gov/pml/div688/localtime.cfm#zones
*
* REVISION HISTORY:
* 20141102 JMO Created
*************************************************************************/
ALTER FUNCTION [dbo].[toUSTZ]
( @thedate datetime2
, @timezone char(2)
)
RETURNS datetimeoffset(0)
AS
BEGIN
DECLARE @offsettime datetimeoffset(0)
, @tzoffset varchar(10)
--TODO: need function for second sunday in march and first sunday in november
-- or make lookup table - but would it be more efficient?
IF @thedate BETWEEN '20100314 02:00:00' AND '20101107 01:59:59'
OR @thedate BETWEEN '20110313 02:00:00' AND '20111106 01:59:59'
OR @thedate BETWEEN '20120311 02:00:00' AND '20121104 01:59:59'
OR @thedate BETWEEN '20130310 02:00:00' AND '20131103 01:59:59'
OR @thedate BETWEEN '20140309 02:00:00' AND '20141102 01:59:59'
OR @thedate BETWEEN '20150308 02:00:00' AND '20151101 01:59:59'
OR @thedate BETWEEN '20160313 02:00:00' AND '20161106 01:59:59'
OR @thedate BETWEEN '20170312 02:00:00' AND '20171105 01:59:59'
OR @thedate BETWEEN '20180311 02:00:00' AND '20181104 01:59:59'
BEGIN
SELECT @tzoffset = CASE @timezone
WHEN 'UT' THEN '+00:00'
WHEN 'AT' THEN '-04:00'
WHEN 'ET' THEN '-04:00'
WHEN 'CT' THEN '-05:00'
WHEN 'MT' THEN '-06:00'
WHEN 'AZ' THEN '-07:00'
WHEN 'PT' THEN '-07:00'
WHEN 'AK' THEN '-08:00'
WHEN 'HT' THEN '-10:00'
WHEN 'ST' THEN '-11:00'
WHEN 'CH' THEN '+10:00'
ELSE NULL
END
END
ELSE
BEGIN
SELECT @tzoffset = CASE @timezone
WHEN 'UT' THEN '+00:00'
WHEN 'AT' THEN '-04:00'
WHEN 'ET' THEN '-05:00'
WHEN 'CT' THEN '-06:00'
WHEN 'MT' THEN '-07:00'
WHEN 'AZ' THEN '-07:00'
WHEN 'PT' THEN '-08:00'
WHEN 'AK' THEN '-09:00'
WHEN 'HT' THEN '-10:00'
WHEN 'ST' THEN '-11:00'
WHEN 'CH' THEN '+10:00'
ELSE NULL
END
END
SET @offsettime = todatetimeoffset( @thedate, @tzoffset )
RETURN(@offsettime)
END --f_toUSTZ
GO
/*************************************************************************
* NAME:
* dbo.toUTC
*
* TYPE:
* Function
*
* DESCRIPTION:
* Converts to UTC DateTimeOffset accounting for Daylight Savings Time
*
* RETURNS:
* datetimeoffset(0) (ie: '2014-10-31 18:25:51 +00:00')
*
* PARAMETERS:
* @thedate datetime2 --the date to be converted
* @timezone char(2) --the date's US time zone
* Zone ST DST
* UT +0 +0 Universal Time
* AT -4 -4 Atlantic
* ET -5 -4 Eastern
* CT -6 -5 Central
* MT -7 -6 Mountain
* AZ -7 -7 Arizona
* PT -8 -7 Pacific
* AK -9 -8 Alaska
* HT -10 -10 Hawaii
* ST -11 -11 Samoa
* CH +10 +10 Chamorro
* REF: http://www.nist.gov/pml/div688/localtime.cfm#zones
*
* REFERENCES:
* dbo.toUSTZ
*
* REVISION HISTORY:
* 20141102 JMO Created
*************************************************************************/
CREATE FUNCTION [dbo].[toUTC]
( @thedate datetime2
, @timezone char(2)
)
RETURNS datetimeoffset(0)
AS
BEGIN
DECLARE @offsettime datetimeoffset(0)
, @utctime datetimeoffset(0)
SET @offsettime = dbo.toUSTZ( @thedate, @timezone )
SET @utctime = switchoffset( @offsettime, '+00:00' )
RETURN(@utctime)
END --toUTC
GO