How to Convert UTC to Local Time Zone in SQL Server

When working with data in SQL Server, dealing with time zones can be tricky, especially when your data is stored in UTC (Coordinated Universal Time) but needs to be presented in a specific local time zone. Fortunately, SQL Server provides several methods to handle time zone conversions. In this tutorial, we’ll guide you through the process of converting UTC to a local time zone using different techniques.

1. Using `SWITCHOFFSET` to Convert UTC to Local Time Zone

SQL Server offers the `SWITCHOFFSET` function, which allows you to adjust a `datetimeoffset` value by a specified time zone. This function is very useful for converting UTC times to local times. Below is an example of how to use it:

SELECT SWITCHOFFSET (CAST('2025-05-08 12:00:00 +00:00' AS datetimeoffset), '-05:00') AS LocalTime;
                                

In this example, the UTC time is being converted to Eastern Standard Time (EST), which is UTC-5 hours.

2. Using `AT TIME ZONE` (SQL Server 2016 and Later)

If you’re using SQL Server 2016 or later, you can take advantage of the `AT TIME ZONE` feature to convert between time zones. Here’s how you can convert a UTC time to a specific time zone:

SELECT CAST('2025-05-08 12:00:00' AS datetime) AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS LocalTime;
                                

This example converts the UTC time to Pacific Standard Time (PST). The `AT TIME ZONE` function automatically handles the time zone conversion, including daylight saving adjustments when applicable.

3. Manual Conversion Using `DATEADD` and `DATEDIFF` Functions

If you're using an older version of SQL Server that doesn’t support the `AT TIME ZONE` feature, you can manually adjust the UTC time using the `DATEADD` and `DATEDIFF` functions. Here’s an example of how to manually convert UTC to a specific local time zone:

SELECT DATEADD(HOUR, -5, '2025-05-08 12:00:00') AS LocalTime;
                                

This example subtracts 5 hours from the UTC time to convert it to Eastern Standard Time.

4. Handling Daylight Saving Time (DST)

When converting time zones, it's important to take into account daylight saving time (DST) changes. If you're using `AT TIME ZONE`, SQL Server will automatically adjust for DST based on the time zone you specify. However, when manually converting time zones, you will need to account for DST yourself, either by adjusting the conversion based on the date or using a lookup table with DST rules.

Conclusion

Converting UTC to local time zones in SQL Server is a common task, and there are several methods available to handle this depending on your SQL Server version. For newer versions, the `AT TIME ZONE` function offers a convenient and reliable way to handle time zone conversions, while older versions can rely on functions like `SWITCHOFFSET` or manual adjustments using `DATEADD` and `DATEDIFF`. Always remember to consider daylight saving time when working with local time zones to ensure accurate results.