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

Question

I just need to change the date format from datatimeoffset to datetime while pulling the data

Answer 1

I will quote to "RichardTheKiwi"

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

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)

---

For more information you can visit: How can I convert a Sql Server 2008 DateTimeOffset to a DateTime