title: How to cast datetime to datetimeoffset?
body: <p>How can i convert an SQL Server <code>datetime</code> value to a <code>datetimeoffset</code> value?</p>
  <hr />
  <p>For example, an existing table contains <code>datetime</code> values that are all in <em>&quot;local&quot;</em> server time.</p>
  <pre><code>SELECT TOP 5 ChangeDate FROM AuditLog
  
  ChangeDate
  =========================
  2013-07-25 04:00:03.060
  2013-07-24 04:00:03.073
  2013-07-23 04:00:03.273
  2013-07-20 04:00:02.870
  2013-07-19 04:00:03.780
  </code></pre>
  <p>My server (<em>happens</em>) to be (right now, today) four hours behind UTC (right now, in the U.S. Eastern timezone, with Daylight Savings active):</p>
  <pre><code>SELECT SYSDATETIMEOFFSET()
  
  2013-07-25 14:42:41.6450840 -04:00
  </code></pre>
  <p>i want to convert the stored <code>datetime</code> values into <code>datetimeoffset</code> values; using the server's current timezone offset information.</p>
  <p>The values i <strong>desire</strong> are:</p>
  <pre><code>ChangeDate               ChangeDateOffset
  =======================  ==================================
  2013-07-25 04:00:03.060  2013-07-25 04:00:03.0600000 -04:00
  2013-07-24 04:00:03.073  2013-07-24 04:00:03.0730000 -04:00
  2013-07-23 04:00:03.273  2013-07-23 04:00:03.2730000 -04:00
  2013-07-20 04:00:02.870  2013-07-20 04:00:02.8700000 -04:00
  2013-07-19 04:00:03.780  2013-07-19 04:00:03.7800000 -04:00
  </code></pre>
  <p>You can see the desirable characteristics:</p>
  <pre><code>2013-07-19 04:00:03.7800000 -04:00
  \_________________________/ \____/
             |                  |
        a &quot;local&quot; datetime      the offset from UTC
  </code></pre>
  <p>But instead the actual values are:</p>
  <pre><code>SELECT TOP 5
     ChangeDate,
     CAST(ChangeDate AS datetimeoffset) AS ChangeDateOffset
  FROM AuditLog
  
  ChangeDate               ChangeDateOffset
  =======================  ==================================
  2013-07-25 04:00:03.060  2013-07-25 04:00:03.0600000 +00:00
  2013-07-24 04:00:03.073  2013-07-24 04:00:03.0730000 +00:00
  2013-07-23 04:00:03.273  2013-07-23 04:00:03.2730000 +00:00
  2013-07-20 04:00:02.870  2013-07-20 04:00:02.8700000 +00:00
  2013-07-19 04:00:03.780  2013-07-19 04:00:03.7800000 +00:00
  </code></pre>
  <p>With the invalid characteristics:</p>
  <pre><code>2013-07-19 04:00:03.7800000 +00:00
  \_________________________/ \____/
                                ^
                                |
                               No offset from UTC present
  </code></pre>
  <p>So i try other things randomly:</p>
  <pre><code>SELECT TOP 5
      ChangeDate, 
      CAST(ChangeDate AS datetimeoffset) AS ChangeDateOffset,
      DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), ChangeDate) AS ChangeDateUTC,
      CAST(DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), ChangeDate) AS datetimeoffset) AS ChangeDateUTCOffset,
      SWITCHOFFSET(CAST(ChangeDate AS datetimeoffset), DATEDIFF(minute, GETUTCDATE(), GETDATE())) AS ChangeDateSwitchedOffset
  FROM AuditLog
  ORDER BY ChangeDate DESC
  </code></pre>
  <p>With results:</p>
  <pre><code>ChangeDate               ChangeDateOffset                    ChangeDateUTC            ChangeDateUTCOffset                 ChangeDateSwitchedOffset
  =======================  ==================================  =======================  ==================================  ==================================
  2013-07-25 04:00:03.060  2013-07-25 04:00:03.0600000 +00:00  2013-07-25 08:00:03.060  2013-07-25 08:00:03.0600000 +00:00  2013-07-25 00:00:03.0600000 -04:00
  2013-07-24 04:00:03.073  2013-07-24 04:00:03.0730000 +00:00  2013-07-24 08:00:03.073  2013-07-24 08:00:03.0730000 +00:00  2013-07-24 00:00:03.0730000 -04:00
  2013-07-23 04:00:03.273  2013-07-23 04:00:03.2730000 +00:00  2013-07-23 08:00:03.273  2013-07-23 08:00:03.2730000 +00:00  2013-07-23 00:00:03.2730000 -04:00
  2013-07-20 04:00:02.870  2013-07-20 04:00:02.8700000 +00:00  2013-07-20 08:00:02.870  2013-07-20 08:00:02.8700000 +00:00  2013-07-20 00:00:02.8700000 -04:00
  2013-07-19 04:00:03.780  2013-07-19 04:00:03.7800000 +00:00  2013-07-19 08:00:03.780  2013-07-19 08:00:03.7800000 +00:00  2013-07-19 00:00:03.7800000 -04:00
                           ----------------------------------                           ----------------------------------  ----------------------------------
                                                No UTC offset                           Time in UTC          No UTC offset  Time all wrong
  </code></pre>
  <p>None of them return the desired values.</p>
  <p>Can anyone suggest something that returns what i intuitively want?</p>