title: Functions to convert dates between local time and UTC time by considering US day light savings time
body: <p>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.</p>
  
  <pre><code>-- 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 &lt; 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)
  </code></pre>