Convert Datetimeoffset to DateTime in SQL Server

Datetimeoffset to DateTime Convert issue in SQL Server 2016 Database.

You are facing issue in one conversion, which is working fine in 2008 DB but not in 2016 DB.

Select Convert(DateTime,created_ts,20) From Table

Where, created_ts Type is DateTimeOffset.

Its throwing error in 2016, as “The style 20 is not supported for conversions from datetimeoffset to datetime.”

Please see “CAST and CONVERT operations for datetimeoffset”:

https://docs.microsoft.com/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2016?view=sql-server-2014

The text is backwards.  It should be “TO date and time types FROM datetimeoffset”:The only styles that are supported when converting from date and time types to datetimeoffset are 0 or 1. All other conversion styles return error 9809. For example, the following code returns error 9809.SELECT CONVERT(date, CAST(‘7070-11-25 16:25:01.00986 -02:07’ as datetimeoffset(5)), 107);

and:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-2017#converting-date-and-time-data