How to convert DateTimeOffeset to DateTime in SQL Server

Question

How to convert DateTimeOffset to DateTime?

I have tried using:

CONVERT(VARCHAR, 2018-03-02 12:00:00.0000000 -07:00, 120)

but the result is:

"2018-03-02 12:00:00 -07:00"

Ex: "2018-03-02 12:00:00.0000000 -07:00" to "2018-03-02 12:00:00"

Does this satisfy your needs? https://stackoverflow.com/questions/4953903/how-can-i-convert-a-sql-server-2008-datetimeoffset-to-a-datetime — Phil N DeBlanc, Mar 05 '18 at 20:30

Phil N DeBlanc · Accepted Answer · 2018-03-05T21:27:23.600

0

According to the site I linked to above, and in my own testing, this works:

select select CONVERT(VARCHAR(19), CONVERT(DATETIME, SWITCHOFFSET('2018-03-02 12:00:00.0000000 -07:00', DATEPART(tz, SYSDATETIMEOFFSET()))),120)

That should do it!

edited Mar 05 '18 at 21:27

answered Mar 05 '18 at 20:39

Phil N DeBlanc

398
1
13

Thanks Phil.It worked but I can't get rid of milliseconds the result is "2018-03-02 12:00:00.000" instead of "2018-03-02 12:00:00" – K.Tom Mar 05 '18 at 20:47
@K.Tom - How about now? – Phil N DeBlanc Mar 05 '18 at 21:27

score 0 · Answer 2 · edited Mar 05 '18 at 21:37

0

Please use below conversion

declare @createdate datetimeoffset
set @createdate = '2018-03-02 12:00:00.0000000 -07:00'
select convert(datetimeoffset,CONVERT(datetime2, @createdate , 1))

edited Mar 05 '18 at 21:37

rollstuhlfahrer

3,988
9
25
38

answered Mar 05 '18 at 21:15

Sooraj

1

Did you try that before posting it? It's not even close to what he's after. – Phil N DeBlanc Mar 05 '18 at 21:19
While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – rollstuhlfahrer Mar 05 '18 at 21:37

How to convert DateTimeOffeset to DateTime in SQL Server

2 Answers2