title: Need a SQL Server function to convert local datetime to UTC that supports DST
body: <p>We are migrating an application to Azure, but currently all of our dates are stored as Eastern Standard Time. Since SQL Azure is on UTC and many of our dates are generated from a getdate() call, we're going to have issues if we leave everything as it is. It seems that the option that will provide the least long term headache is to just convert all of our stored dates to UTC, and have them converted to local time on the front end.</p>
  
  <p>Unfortunately it seems that there isn't a built-in way to convert a SQL datetime from one time zone to another, and many of the solutions that I've found don't take daylight savings time into consideration. The solutions that I've found that do consider DST involve importing calendars and time zone tables and are extremely complex.</p>
  
  <p>I'm just looking for something that will work for a single conversion, and it doesn't have to be pretty.  Any ideas?</p>
  
  <p><strong>UPDATE:</strong> I wrote the following function that I believe will accomplish what I need.  Does anyone see any holes with this?  All of my dates are currently Eastern Standard Time, but my dates start around 2002 so I had to handle that law change in 2007.  </p>
  
  <p>I'm thinking I would use this function like this:</p>
  
  <pre><code>UPDATE myTable SET myDate = dbo.fnESTtoUTC(myDate)
  </code></pre>
  
  <p>Here's the function:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[fnESTtoUTC]
      (@pESTDate  DATETIME)
      RETURNS DATETIME
  AS
  BEGIN
  
  DECLARE @TimeZoneOffset INT
  DECLARE @Year INT
  DECLARE @Day INT
  DECLARE @DSTStart DATETIME
  DECLARE @DSTEnd DATETIME
  
  SELECT @Year = DATEPART(year, @pESTDate)
  
  IF @Year &gt;= 2007
  BEGIN
      SELECT @Day = 8
      WHILE @DSTStart IS NULL
      BEGIN
          -- Second Sunday in March
          IF DATEPART(weekday, DATEFROMPARTS(@Year, 3, @Day)) = 1
              SELECT @DSTStart = DATETIMEFROMPARTS(@Year, 3, @Day, 2, 0, 0, 0)
          SELECT @Day = @Day + 1
      END
  
      SELECT @Day = 1
      WHILE @DSTEnd IS NULL
      BEGIN
          -- First Sunday in November
          IF DATEPART(weekday, DATEFROMPARTS(@Year, 11, @Day)) = 1
              SELECT @DSTEnd = DATETIMEFROMPARTS(@Year, 11, @Day, 1, 0, 0, 0)
          SELECT @Day = @Day + 1
      END
  END
  ELSE
  BEGIN
      SELECT @Day = 1
      WHILE @DSTStart IS NULL
      BEGIN
          -- First Sunday in April
          IF DATEPART(weekday, DATEFROMPARTS(@Year, 4, @Day)) = 1
              SELECT @DSTStart = DATETIMEFROMPARTS(@Year, 4, @Day, 2, 0, 0, 0)
          SELECT @Day = @Day + 1
      END
  
      SELECT @Day = 31
      WHILE @DSTEnd IS NULL
      BEGIN
          -- Last Sunday in October
          IF DATEPART(weekday, DATEFROMPARTS(@Year, 10, @Day)) = 1
              SELECT @DSTEnd = DATETIMEFROMPARTS(@Year, 10, @Day, 1, 0, 0, 0)
          SELECT @Day = @Day - 1
      END
  END
  
  IF @pESTDate &gt;= @DSTStart AND @pESTDate &lt; @DSTEnd
  BEGIN
      -- Date is in DST
      SELECT @TimeZoneOffset = 4
  END
  ELSE
  BEGIN
      -- Not DST
      SELECT @TimeZoneOffset = 5
  END
  
  RETURN ( dateadd(hh, @TimeZoneOffset, @pESTDate) )
  END
  </code></pre>