How to cast datetime to datetimeoffset?

Question

How can i convert an SQL Server datetime value to a datetimeoffset value?

---

For example, an existing table contains datetime values that are all in "local" server time.

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 

My server (happens) to be (right now, today) four hours behind UTC (right now, in the U.S. Eastern timezone, with Daylight Savings active):

SELECT SYSDATETIMEOFFSET()  2013-07-25 14:42:41.6450840 -04:00 

i want to convert the stored datetime values into datetimeoffset values; using the server's current timezone offset information.

The values i desire are:

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 

You can see the desirable characteristics:

2013-07-19 04:00:03.7800000 -04:00 \_________________________/ \____/            |                  |       a "local" datetime      the offset from UTC 

But instead the actual values are:

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 

With the invalid characteristics:

2013-07-19 04:00:03.7800000 +00:00 \_________________________/ \____/                               ^                               |                              No offset from UTC present 

So i try other things randomly:

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 

With results:

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 

None of them return the desired values.

Can anyone suggest something that returns what i intuitively want?

Answer 1

Edit: Updated better answer for SQL Server 2016

SELECT     ChangeDate,  --original datetime value    ChangeDate AT TIME ZONE 'Eastern Standard Time' AS ChangeDateOffset FROM AuditLog 

The AT TIME ZONE takes into account whether daylight savings was in effect at the time of the date being converted. And even though it says "Standard" in "Eastern Standard Time", it will give you daylight times as well:

ChangeDate               ChangeDateOffset -----------------------  ------------------------------ 2019-01-21 09:00:00.000  2019-01-21 09:00:00.000 -05:00 2019-02-21 09:00:00.000  2019-02-21 09:00:00.000 -05:00 2019-03-21 09:00:00.000  2019-03-21 09:00:00.000 -04:00  <-- savings time 2019-04-21 09:00:00.000  2019-04-21 09:00:00.000 -04:00  <-- savings time 2019-05-21 09:00:00.000  2019-05-21 09:00:00.000 -04:00  <-- savings time 2019-06-21 09:00:00.000  2019-06-21 09:00:00.000 -04:00  <-- savings time 2019-07-21 09:00:00.000  2019-07-21 09:00:00.000 -04:00  <-- savings time 2019-08-21 09:00:00.000  2019-08-21 09:00:00.000 -04:00  <-- savings time 2019-09-21 09:00:00.000  2019-09-21 09:00:00.000 -04:00  <-- savings time 2019-10-21 09:00:00.000  2019-10-21 09:00:00.000 -04:00  <-- savings time 2019-11-21 09:00:00.000  2019-11-21 09:00:00.000 -05:00 2019-12-21 09:00:00.000  2019-12-21 09:00:00.000 -05:00 

As for how do you avoid hard-coding the string Eastern Standard Time, and use the current timezone of the server? You're SOL.

Original pre-SQL Server 2016 answer

i figured it out. The trick is that there is a built-in SQL Server function ToDateTimeOffset, which attaches arbitrary offset information to any supplied datetime.

For example, the identical queries:

SELECT ToDateTimeOffset('2013-07-25 15:35:27', -240)     --  -240 minutes SELECT ToDateTimeOffset('2013-07-25 15:35:27', '-04:00') --  -4 hours 

both return:

2013-07-25 15:35:27.0000000 -04:00 

Note: The offset parameter to ToDateTimeOffset can either be:

• an integer, representing a number of minutes
• a string, representing a hours and minutes (in {+|-}TZH:THM format)

We need the server's current UTC offset

Next we need the server's current offset from UTC. There are two ways i can have SQL Server return the the integer number of minutes we are from UTC:

DATEPART(TZOFFSET, SYSDATETIMEOFFSET())  DATEDIFF(minute, GETUTCDATE(), GETDATE()) 

both return

-240 

Plugging this into the TODATETIMEOFFSET function:

SELECT ToDateTimeOffset(       '2013-07-25 15:35:27',       DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) --e.g. -240 ) 

returns the datetimeoffset value i want:

2013-07-25 15:35:27.0000000 -04:00 

Putting it altogether

Now we can have a better function to convert a datetime into a datetimeoffset:

CREATE FUNCTION dbo.ToDateTimeOffset(@value datetime2)     RETURNS datetimeoffset AS BEGIN /*     Converts a date/time without any timezone offset into a datetimeoffset value,      using the server's current offset from UTC.           For this we use the built-in ToDateTimeOffset function;      which attaches timezone offset information with a datetimeoffset value.          The trick is to use DATEDIFF(minutes) between local server time and UTC      to get the offset parameter.          For example:         DATEPART(TZOFFSET, SYSDATETIMEOFFSET())     returns the integer         -240      for people in EDT (Eastern Daylight Time), which is 4 hours (240 minutes) behind UTC.     Pass that value to the SQL Server function:         TODATETIMEOFFSET(@value, -240) */          RETURN TODATETIMEOFFSET(@value, DATEPART(TZOFFSET, SYSDATETIMEOFFSET())) END; 

Sample usage

SELECT TOP 5     ChangeDate,      dbo.ToDateTimeOffset(ChangeDate) AS ChangeDateOffset FROM AuditLog 

returns the desired:

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 

It would have been ideal if the built-in function would have just did this:

TODATETIMEOFFSET(value) 

rather than having to create an "overload":

dbo.ToDateTimeOffset(value) 

Note: Any code is released into the public domain. No attribution required.

Answer 2

To convert from a local time to a datetimeoffset with the current time offset seems to take some trickery. There's probably a simpler way, but this seems to do it;

SELECT ChangeDate,    CONVERT(DATETIMEOFFSET, CONVERT(VARCHAR, ChangeDate, 120) +            RIGHT(CONVERT(VARCHAR, SYSDATETIMEOFFSET(), 120), 6), 120) FROM AuditLog; 

It's probably worth creating a function;

CREATE FUNCTION LOCALIFY(@dt DATETIME)    RETURNS DATETIMEOFFSET AS BEGIN  RETURN CONVERT(DATETIMEOFFSET,            CONVERT(VARCHAR, @dt, 120) +            RIGHT(CONVERT(VARCHAR, SYSDATETIMEOFFSET(), 120), 6), 120) END; 

...and then just...

SELECT ChangeDate, dbo.LOCALIFY(ChangeDate) FROM AuditLog;