How to Convert SQL Server 2008 DATETIMEOFFSET to DATETIME While Preserving the Offset?

Converting DATETIMEOFFSET to DateTime While Preserving Offset

In SQL Server 2008, converting a DATETIMEOFFSET field to a DATETIME field typically results in the loss of time information due to the offset. However, there is a way to convert the value while retaining the offset, effectively converting it to UTC.

The key to this conversion lies in using the CONVERT function with the correct style argument. The style argument specifies how the conversion should be performed. For this specific purpose, we need to use style 1:

SELECT CONVERT(datetime2, @createdon, 1)

The above query takes a DATETIMEOFFSET variable (@createdon) and converts it to a DATETIME2 value using style 1. This style converts the DATETIMEOFFSET value to a DATETIME2 value in UTC by subtracting the offset.

For example, consider the following DATETIMEOFFSET value:

2008-12-19 17:30:09.0000000 +11:00

If we convert this value using style 1, we get:

2008-12-19 06:30:09.0000000

This result is in UTC time, effectively removing the offset.

Note that converting from DATETIME2 to DATETIMEOFFSET using style 1 simply sets the offset to 00:00. This can be used as a quick method to convert a DATETIMEOFFSET value with a non-zero offset to a DATETIMEOFFSET value in UTC:

SELECT convert(datetimeoffset,CONVERT(datetime2, @createdon, 1))

The above is the detailed content of How to Convert SQL Server 2008 DATETIMEOFFSET to DATETIME While Preserving the Offset?. For more information, please follow other related articles on the PHP Chinese website!