When working with datetime values in SQL Server, there may be times when you need to convert them to UTC (Coordinated Universal Time). This is especially important when dealing with international data or when you need to perform calculations or comparisons across different time zones. In this article, we will explore how to convert datetime values to UTC in SQL Server.

Prior to SQL Server 2016, many developers used the DATEADD and DATEDIFF functions to convert datetime values to UTC. However, this approach is not reliable when it comes to handling daylight savings time changes. The results may vary depending on the time of the year.

Fortunately, SQL Server 2016 introduced the DATETIMEOFFSET data type and the AT TIME ZONE clause, which provide a more accurate and deterministic way to convert datetime values to UTC.

Here is an example of how to convert a datetime value to UTC using the DATETIMEOFFSET data type and the AT TIME ZONE clause:

DECLARE @dt DATETIME = '2020-09-22 22:23:13.920';
SELECT CONVERT(DATETIME,
    CONVERT(DATETIMEOFFSET,
        CONVERT(DATETIMEOFFSET,
            @dt AT TIME ZONE 'Central European Standard Time'
        )
        AT TIME ZONE 'UTC'
    )
);

In the above example, we first convert the input datetime value to the local timezone (‘Central European Standard Time’), then convert it to UTC, and finally convert the result back to a datetime value. This ensures that the conversion is accurate and takes into account any daylight savings time changes.

If you are using a version of SQL Server prior to 2016, it is recommended to upgrade to a newer version that supports the DATETIMEOFFSET data type and the AT TIME ZONE clause. However, if upgrading is not an option, you can create a table with all the start and end dates of daylight savings time for each year and use it to look up the offset.

Update: It has been pointed out that the conversion to DATETIMEOFFSET is not necessary, making the code even shorter. Here is the updated code:

DECLARE @dt DATETIME = '2020-02-22 22:23:13.920';
SELECT CONVERT(DATETIME,
    @dt AT TIME ZONE 'Central European Standard Time'
    AT TIME ZONE 'UTC');

By using the AT TIME ZONE clause, you can easily convert datetime values to UTC in a reliable and deterministic manner.

Thank you for reading this article. We hope you found it helpful in understanding how to convert datetime values to UTC in SQL Server.

The post Converting a Datetime to UTC first appeared on Under the kover of business intelligence.