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?

Buy

Ok, I'm probably blind, but what's the difference between your desired result and your actual result? – Cote

@JoachimIsaksson Sorry, you're right. i copy-pasted the two (as i was in a rush). i fixed the "actual", and the formatting of the full list of attempts – Buy

I realize this post is quite old, but for future consideration, folks could look at using the new AT TIME ZONE clause (added in SQL 2016). This seems to work for me: select (SomeDate AT TIME ZONE 'Eastern Standard Time') AT TIME ZONE 'UTC' – Witling

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.

Buy

This is pretty good - except that it assumes that the server's current offset is the one that is in effect for all data. If the original times were recorded in a time zone that alternates between a standard offset and a daylight offset, then this doesn't account for that. – Digression

This was helpful. Instead of using DateDiff from UTC to get the current server offset you can just do this: DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) – Epirogeny

It's been 2 years ago since this post, but as @MattJohnson stated, I have data that goes back 3 years, and this doesn't take into account when we switch from Standard to Daylight Savings Time and back. Anyone found a better way? – Ostensive

@Ostensive - SQL Server 2016 adds time zone support, or you can use my project. Info on both are here: github.com/mj1856/SqlServerTimeZoneSupport – Digression

@MattJohnson: hi Matt! I just commented on a related thread. Thanks for the SQL extension very helpful – Hautboy

So, if your server is in a timezone with daylight savings, and the date being queried is from 6 months ago, the result will be off by an hour, because it applies the current offset which would be the opposite DST status than the queried date. Right? – Glauce

@Glauce That is absolutely correct. The one-time conversion of historical data will be wrong - that's the price we pay for not storing UTC in a datetime all these years. – Buy

Warning: AT TIME ZONE has performance issues on large datasets: sqlskills.com/blogs/jonathan/at-time-zone-performance-issues – Audreaaudres

@Glauce Update: The new way, using SQL Server 2016's AT TIME ZONE 'Eastern Standard Time', does convert historical dates correctly. Windows knows what whether daylight savings was in effect at the time of the datetime you're converting, and will convert it correctly. – Buy

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;

Cote

Oddly enough, Joachim, your answer helped me. i was about to create the function you had, but i wanted a better name than Localify. i decided that ToDateTimeOffset is exactly what i want; it describes exactly what i'm trying to do. That's when i realized that there already is a TODATETIMEOFFSET; except it doesn't do what anyone would expect. – Buy

Answer 3

You can use AT TIME ZONE (SQL Server 2016) along with CURRENT_TIMEZONE_ID (SQL Server 2022) like so:

SELECT CURRENT_TIMEZONE_ID();

DECLARE @tests TABLE (dt DATETIME2(0) NOT NULL);

INSERT INTO @tests (dt) VALUES
('2023-03-11 12:00:00'),
('2023-03-12 12:00:00'),
('2023-11-04 12:00:00'),
('2023-11-05 12:00:00');

SELECT dt, dt AT TIME ZONE CURRENT_TIMEZONE_ID()
FROM @tests;

Ore

Warning: AT TIME ZONE has performance issues on large datasets: sqlskills.com/blogs/jonathan/at-time-zone-performance-issues – Audreaaudres

@rahul correct. SQL server does not maintain time zone rules database, Windows does. These rules can change unexpectedly (government decides, not programmers) and some mechanism (e.g. Windows update) is needed to keep this data up-to-date. The at time zone conversion needs to lookup this information i.e. what time zone offset was used by country x on datetime y. This lookup into the Windows API is time consuming. – Ore

Answer 4

It is a bit later in time from the OP, but this thread is helpful in noting methods of converting datetime to datetimeoffset.

I had used some of the functionality, but would also suggest using a field with the default set to sysdatetimeoffset(), so that as items were inserted (the current timestamp) would be relative to when it was put in. Then if modifications are required, the update could utilize the TZ from the source in the procedure.

This has become especially evident in OData v4 transactions, which require datetimeoffset.

Woodenhead

Answer 5

I think you have to multiply the DATEPART(TZOFFSET,SYSDATETIMEOFFSET()) by -1 in order to get the correct TimeZone Offset. I think, if you are in Eastern time zone, the TimeZone offset should be +4:00 instead of -4:00. Is the offset from my local server to UTC or from UTC to my local server?

Theola

Huh? EST is behind UTC isn't it? – Amhara

The offset is added to UTC to get your local time. So if it is 23:00 in UTC, then in a zone with an offset of -0400, the time would be 19:00. – Celestecelestia