How to Convert SQL Server DATETIMEOFFSET to UTC DATETIME?

Converting SQL Server DATETIMEOFFSET to DATETIME in UTC

In SQL Server, you may encounter a scenario where you need to convert a DATETIMEOFFSET field to a DATETIME field while adjusting the time to compensate for the offset, effectively converting it to UTC.

To achieve this, you can utilize the CONVERT function as follows:

SELECT CONVERT(datetime2, @createdon, 1)

Here, @createdon represents your DATETIMEOFFSET field. The number 1 in the second parameter specifies that the conversion should be performed from the local time zone to UTC.

For example, let's consider a DATETIMEOFFSET value:

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

Upon applying the CONVERT function, it will be converted to:

2008-12-19 06:30:09.0000000

This corresponds to the same time in UTC.

Additionally, you can use the following method to explicitly set the offset to 00:00 when converting to DATETIMEOFFSET:

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

This will produce the following result:

2008-12-19 06:30:09.0000000 +00:00

Which indicates a DATETIMEOFFSET value in UTC.

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