How can I convert datetimeoffset to a datetime in SQL Server 2008?

Question

I have tried this one, but getting wrong output!

declare @createdon datetimeoffset
set @createdon = '2017-09-16 05:50:56.0000000 +05:30'

select 
    convert(datetime2, @createdon, 1)

select 
    convert(datetimeoffset, convert(datetime2, @createdon, 1))

basically In what Time zone you wan't to convert the Datetime data — Alfaiz Ahmed, Sep 16 '17 at 09:18
@AlfaizAhmed ,There is a chance to data from multiple nations to that particular table.so can't mention the Time zone.Now i m checking IST alone(UTC +5:30) — Guna, Sep 16 '17 at 09:34
@Guna how will you Recognize which Time Zone Data you Store in Table — Alfaiz Ahmed, Sep 18 '17 at 05:36

score 0 · Answer 1 · answered Sep 16 '17 at 07:01

0

select CONVERT(VARCHAR(20), @createdon, 120)

answered Sep 16 '17 at 07:01

The beginner

624
4
17

That @createdon varibale value is today's 11.20 am alone..but after converting it shows another time – Guna Sep 16 '17 at 07:06
Now i have used like this.. SELECT DATEADD ( MINUTE, (DATEPART(HOUR,CAST(RIGHT(@createdon,5) AS DATETIME)) * 60 + DATEPART(MINUTE,CAST(RIGHT(@createdon,5) AS DATETIME))), CAST(@createdon AS DATETIME) ) – Guna Sep 16 '17 at 08:08

How can I convert datetimeoffset to a datetime in SQL Server 2008?

1 Answers1