Microsoft® SQL Server 2008 R2 Unleashed

Date and Time Conversions

If an existing CONVERT style includes the time part, and the conversion is from datetimeoffset to a string, the time zone offset (except for style 127) is included. If you do not want the time zone offset, you need to use cast or convert the datetimeoffset value to datetime2 first and then to a string:

When you convert from datetime2 or datetimeoffset to date, there is no rounding and the date part is extracted explicitly. For any implicit conversion from datetimeoffset to date, time, datetime2, datetime, or smalldatetime, conversion is based on the local date and time value (to the persistent time zone offset). For ...