How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
Converting using almost any style will cause the datetime2 value to be converted to UTC.
Also, conversion from datetime2 to datetimeoffset simply sets the offset at +00:00
, per the below, so it is a quick way to convert from Datetimeoffset(offset!=0)
to Datetimeoffset(+00:00)
declare @createdon datetimeoffset
set @createdon = '2008-12-19 17:30:09.1234567 +11:00'
select CONVERT(datetime2, @createdon, 1)
--Output: 2008-12-19 06:30:09.12
select convert(datetimeoffset,CONVERT(datetime2, @createdon, 1))
--Output: 2008-12-19 06:30:09.1234567 +00:00
Convert datetimeoffset in varchar to date / datetime
If the +0000 section of your string is supposed to represent the timezone offset, it is malformed as far as cast/datetimeoffset is concerned.
It should be in the format +hh:mm - if i try casting 2019-11-04T16:25:15.000+00:00, it works fine.
MS SQL reference here:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver15
Update: incorporating SalmanA's comments:
select
CAST('2019-11-04 16:25:15+09:00' AS datetimeoffset(7)) as [No T],
CAST('2019-11-04T16:25:15+09:00' AS datetimeoffset(7)) as [With T]
returns (in SQL 2016)
[No T] [With T]
2019-11-04 16:25:15.0000000 +09:00 2019-11-04 16:25:15.0000000 +09:00
showing that the T being present or not doesn't matter.
How to cast datetime to datetimeoffset?
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.
Related Topics
Delete SQL Rows Where Ids Do Not Have a Match from Another Table
Protecting Against SQL Injection in Python
Custom Date/Time Formatting in SQL Server
Reference Alias in Where Clause
Differencebetween Function and Procedure in Pl/Sql
Insert an Image in Postgresql Database
When How to Save JSON or Xml Data in an SQL Table
What Does the Colon Sign ":" Do in a SQL Query
How to Remove Redundant Namespace in Nested Query When Using for Xml Path
Why Do People Hate SQL Cursors So Much
Calculate a Sum of Type Time Using SQL
How to Create Table Using Select Query in SQL Server
Postgres Column "X" Does Not Exist
Postgresql: Encoding Problems on Windows When Using Psql Command Line Utility