Microsoft® SQL Server 2012 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 to convert the datetimeoffset value to datetime2 first and then to a string:

Click here to view code image

select  convert(varchar(35), SYSDATETIMEOFFSET(), 121) as datetime_offset,  CONVERT(varchar(30), cast(SYSDATETIMEOFFSET() as datetime2),121) as datetime2Go/* outputdatetime_offset                     datetime2----------------------------------- ------------------------------2013-07-01 12:17:25.0913790 -04:00  2013-07-01 12:17:25.0913790*/

When you CONVERT from datetime2 or datetimeoffset ...