How to cast datetime to datetimeoffset?
Sql ServerInternationalizationSql Server-2008-R2Sql Server Problem Overview
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?
Sql Server Solutions
Solution 1 - Sql Server
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.
Solution 2 - Sql Server
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;
Solution 3 - Sql Server
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
.
Solution 4 - Sql Server
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?