Posted on Leave a comment

Time zone conversion that is Daylight savings time aware

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
Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.