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

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 datetimeoffsetset @createdon = '2008-12-19 17:30:09.1234567 +11:00'select CONVERT(datetime2, @createdon, 1)--Output: 2008-12-19 06:30:09.12select convert(datetimeoffset,CONVERT(datetime2, @createdon, 1))--Output: 2008-12-19 06:30:09.1234567 +00:00

I'd use the built in SQL option:

select SWITCHOFFSET(cast('2008-12-19 17:30:09.0000000 +11:00' as datetimeoffset),'+00:00')

I know this is an old question but, if you want to convert DateTimeOffset to a DateTime, I think you need to take into account the timezone of the server you are converting on. If you just do a CONVERT(datetime, @MyDate, 1) you will simply lose the time zone, which likely results in an incorrect conversion.

I think you first need to switch the offset of the DateTimeOffset value, then do the conversion.

DECLARE @MyDate DATETIMEOFFSET = '2013-11-21 00:00:00.0000000 -00:00';SELECT CONVERT(DATETIME, SWITCHOFFSET(@MyDate, DATEPART(tz,SYSDATETIMEOFFSET())));

The result of converting '2013-11-21 00:00:00.0000000 -00:00' to a DateTime on a server who's offset is -7:00 will be 2013-11-20 17:00:00.000. With the above logic it doesn't mater what the time zone of the server or the offset of the DateTime value, it will be converted to DateTime in the servers time zone.

I believe you need to do this because a DateTime value includes an assumption that the value is in the time zone of the server.