How can I convert a Sql Server 2008 DateTimeOffset to a DateTime

Question

I'm hoping to convert a table which has a DATETIMEOFFSET field, down to a DATETIME field BUT recalculates the time by taking notice of the offset. This, in effect, converts the value to UTC.

eg.

CreatedOn: 2008-12-19 17:30:09.0000000 +11:00

that will get converted to

CreatedOn: 2008-12-19 06:30:09.0000000

or

CreatedOn: 2008-12-19 06:30:09.0000000 + 00:00 -- that's a `DATETIMEOFFSET`, but `UTC`.

Cheers :)

refer to https://stackoverflow.com/questions/39719645/convert-datetime-value-from-one-timezone-to-utc-timezone-using-sql-query — afruzan, Mar 18 '21 at 06:10

score 90 · Accepted Answer · edited Oct 06 '14 at 05:43

90

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

edited Oct 06 '14 at 05:43

Mitch Wheat

295,962
43
465
541

answered Feb 10 '11 at 07:01

RichardTheKiwi

105,798
26
196
262

7

Can one explain what does last argument "1" of CONVERT mean? All examples of this argument use character types as output or input type. Here we convert datetimeoffset to datetime. – Robo Burned Feb 14 '17 at 13:05
1

The `convert()` function takes a 3rd parameter which specifies the format for the output. [https://www.w3schools.com/sql/func_convert.asp](https://www.w3schools.com/sql/func_convert.asp). 1 = "mm/dd/yy" format – Eric Harlan May 23 '17 at 15:44
That third argument doesn't really make any sense for a conversion from `datetimeoffset` to `datetime2`, though. It's for `varchar` conversions. https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql (also W3Schools? so many better options now!) – brianary Jun 13 '17 at 15:58
I suppose the third argument was meant to be in the outer convert (just to print the resulting datetimeoffset in a readable way). It should be like this "select convert(datetimeoffset,CONVERT(datetime2, @createdon), 1)" – Thanasis Ioannidis Apr 17 '18 at 09:01
1

@OzBob You must have misunderstood the question, one of the two options requested *was* to drop the offset. The crux is to convert the time to UTC. Whether or not it keeps +00.00 is neither here nor there – RichardTheKiwi May 01 '18 at 05:26
7

Note the answer from @user166390 below. _"The timezone information is discarded in conversion if no style is specified"_. If you don't specify the third parameter, whether 1 or something else, you'll lose the correct UTC conversion – zola25 Oct 01 '18 at 15:14

score 43 · Answer 2 · edited Apr 23 '15 at 15:23

43

I'd use the built in SQL option:

select SWITCHOFFSET(cast('2008-12-19 17:30:09.0000000 +11:00' as datetimeoffset),'+00:00')

edited Apr 23 '15 at 15:23

gunr2171

16,104
25
61
88

answered Aug 21 '14 at 03:02

Vipeout

439
4
2

1

this is the best answer (supporting sql server 2014 also). using `SWITCHOFFSET(...,0)` built-in function to convert datetimeoffset is simple and clean. – afruzan Mar 18 '21 at 05:45
This does appear to support daylight savings time as well which is nice. – Mark Clancy Jun 28 '22 at 17:20

score 31 · Answer 3 · answered Jun 17 '16 at 19:32

31

I know this is an old question but, if you want to convert DateTimeOffset to a DateTime, I think you need to take into account the timezone of the server you are converting on. If you just do a CONVERT(datetime, @MyDate, 1) you will simply lose the time zone, which likely results in an incorrect conversion.

I think you first need to switch the offset of the DateTimeOffset value, then do the conversion.

DECLARE @MyDate DATETIMEOFFSET = '2013-11-21 00:00:00.0000000 -00:00';
SELECT CONVERT(DATETIME, SWITCHOFFSET(@MyDate, DATEPART(tz,SYSDATETIMEOFFSET())));

The result of converting '2013-11-21 00:00:00.0000000 -00:00' to a DateTime on a server who's offset is -7:00 will be 2013-11-20 17:00:00.000. With the above logic it doesn't mater what the time zone of the server or the offset of the DateTime value, it will be converted to DateTime in the servers time zone.

I believe you need to do this because a DateTime value includes an assumption that the value is in the time zone of the server.

answered Jun 17 '16 at 19:32

Jeremy

44,950
68
206
332

1

Thanks for this - we have been running alot more code in Azure now but querying on-premise databases, it nice to be able to take a DateTimeOffset in the cloud, usually it's UTC, and then convert it to a local time without having to know the local server timezone. Using DateTimeOffeset in the cloud also insulates us if the cloud is not UTC. – Rick Glos Feb 01 '17 at 19:16
1

@Jeremy Apparently you haven't tested the code in the answer 5 years before yours. You do NOT need to take into account the timezone on the server you are converting on. 0600+04:00 is always 0200 (UTC) converted in any timezone. – RichardTheKiwi May 01 '18 at 06:58
2

p/s A datetime value is however you interpret it. It has no timezone assumption at all, which is why datetimeoffset exists. – RichardTheKiwi May 01 '18 at 07:00
@RichardTheKiwi I don't think you're understanding what he's doing. Assume that `@MyDate` is external data. Assume the DB stores the value as a `datetime` with the server's local time (a very common application rule for applications that only ever operate in exactly one time zone). You need to correct the time zone *before* you convert it from a `datetimeoffset`. `SELECT CONVERT(DATETIME, @MyDate)` returns midnight on 2013-11-21 regardless of the server's timezone and regardless of the offset specified in the `datetimeoffset`. It just *drops the offset* without converting to the local time. – Bacon Bits Sep 21 '20 at 15:57

score 17 · Answer 4 · answered Aug 18 '20 at 08:26

17

DateTimeoffset (Timezone) conversion in SQL Server.

SQL Server 2016 (13.x) and later

Exmample

Select GETUTCDATE()
Select Convert(DATETIME, GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')
Select Convert(DATETIME, GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time')

Result will be

2020-08-18 08:22:21.640
2020-08-18 10:22:21.640
2020-08-18 13:52:21.640

answered Aug 18 '20 at 08:26

Sukesh Chand

2,339
2
21
29

2

This is what I was looking for. I needed the time zone offset preserved into the final datetime value, not discarded. type 126 in the other answer returned an error. Thanks! – Jeremy Dec 14 '20 at 08:11
1

EXACTLY what I was looking for, I need the time to convert into the main time block, this should be the highest voted answer!! – Sauron Apr 21 '21 at 14:41
It looks like this does not take daylight saving into account. – Roel Feb 03 '22 at 09:59

score 8 · Answer 5 · 2011-02-13T19:08:02.847

8

Note: The timezone information is discarded in conversion if no style ("126" here) is specified. It might also be discarded in some of the other styles, I don't know -- in any case the following correctly adjusts for the TZ information. See CAST and CONVERT.

select convert(datetime, cast('2008-12-19 17:30:09.0000000 +11:00' as datetimeoffset), 126) as utc;

Happy SQL'ing.

Edit

Not sure if it matters but ... datetime Can't actually store that level of precision/accuracy. If the above is run the fractional seconds will be truncated to 3 digits (and accuracy is less than that). The same-same with datetime2 (and datetimeoffset(7)) produces a non-truncated value:

select convert(datetime2, cast('2008-12-19 17:30:09.1234567 +11:00' as datetimeoffset(7)), 126) as utc;

edited Feb 13 '11 at 19:08

answered Feb 10 '11 at 06:28

what is style 126? and why 126? – Pure.Krome Feb 10 '11 at 09:10
@Pure.Krome See the link to CAST and CONVERT in the reply. 126 because I like ISO 8601 and had to pick one. It's really no different than cyberwiki picking 1. As noted (in both answers) certain styles *may* not take the TZ into account. – Feb 10 '11 at 13:44
2

A quick test in SQL Server 2008 R2 shows that only using style `0` or (equivalently) omitting the style code discard the timezone information. Any of the other codes listed in the MSDN document will preserve it. – Dan J Jul 26 '12 at 17:28
9

I'm finding that trying style 126 causes an error (in SQL Server 2012) as an unsupported style when converting directly from `DATETIMEOFFSET` to `DATETIME2`. I haven't found any other than 0 and 1 that are accepted (but I didn't try all of them). 126 is a valid style for converting `DATETIMEOFFSET` to `VARCHAR`. – Rob Parker Sep 10 '13 at 21:05

score 1 · Answer 6 · answered May 24 '23 at 19:13

Several ways of converting from DateTimeOffset to DateTime2 (UTC or local).

On SQL Server 2019:

DECLARE @dto datetimeoffset = SYSDATETIMEOFFSET();

SELECT @dto as MyDateTimeOffset_EST                                -- 2023-05-24 15:04:59.1321648 -04:00
    ,@dto AT TIME ZONE 'UTC'     as DateTimeOffset_UTC             -- 2023-05-24 19:04:59.1321648 +00:00
    ,SWITCHOFFSET(@dto, 0)       as DateTimeOffset_UTC_also        -- 2023-05-24 19:04:59.1321648 +00:00
    ,CONVERT(datetime2, @dto AT TIME ZONE 'UTC') as DateTime2_Utc  -- 2023-05-24 19:04:59.1321648
    ,CONVERT(datetime2, @dto, 1) as DateTime2_Utc_also             -- 2023-05-24 19:04:59.1321648
    ,CONVERT(datetime2, @dto)    as DateTime2_Local                -- 2023-05-24 15:04:59.1321648
    -- If you want to change to a different time zone:
    ,@dto AT TIME ZONE 'UTC'
       AT TIME ZONE 'Pacific Standard Time' as DateTimeOffset_PST  -- 2023-05-24 12:04:59.1321648 -07:00

score 0 · Answer 7 · edited Jul 26 '19 at 17:36

0

In order to account for daylight savings time, I used the following:

CONVERT(
  DateTime, 
  SWITCHOFFSET(
    CONVERT(
      DateTimeOffset, 
      CONVERT(
        DateTime, 
        [time_stamp_end_of_interval], 
        120
      )
    ),
    DATENAME(
      TzOffset, 
      CONVERT(
        DateTime, 
        [time_stamp_end_of_interval], 
        120
      ) AT TIME ZONE 'Pacific Standard Time'
    )
  )
)
AS GOOD_PST

Note: time_stamp_end_of_interval is a varchar

edited Jul 26 '19 at 17:36

kgrg

1,605
1
12
14

answered Jul 26 '19 at 15:51

Duane Pfeiffer

41
4

using only `SWITCHOFFSET(..., 0)` there is no problem related to daylight savings for converting a datetimeoffset to utc. see https://learn.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#arguments – afruzan Mar 18 '21 at 05:53

How can I convert a Sql Server 2008 DateTimeOffset to a DateTime

7 Answers7

Linked

Related