How Do I Convert SQL Server DATETIMEOFFSET to UTC DATETIME?

Convert Sql Server DATETIMEOFFSET to UTC DATETIME

In SQL Server, DATETIMEOFFSET values store both the date and time, as well as an offset from UTC. To convert a DATETIMEOFFSET field to a DATETIME field while accounting for the offset, effectively converting it to UTC, consider the following:

Converting a DATETIMEOFFSET value to DATETIME2 using CONVERT or CAST will automatically convert the value to UTC by discarding the offset. For example:

DECLARE @createdon DATETIMEOFFSET = '2008-12-19 17:30:09.0000000 +11:00';
SELECT CONVERT(DATETIME2, @createdon, 1);
-- Output: 2008-12-19 06:30:09.0000000

However, converting a DATETIMEOFFSET to DATETIME does not set the offset to UTC. To explicitly convert to DATETIME with an offset of 00:00, use the DATETIMEOFFSET function:

SELECT CONVERT(DATETIMEOFFSET, CONVERT(DATETIME2, @createdon, 1));
-- Output: 2008-12-19 06:30:09.0000000 +00:00

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