sqlsql-serversql-server-2008datetimedatetimeoffset

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


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 :)


Solution

  • 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