Convert UTC to Local Time in SQL Server

by

When dealing with applications that span multiple time zones, you’ll often want to store timestamps in UTC. That’s usually the best practice – it avoids confusion and ensures consistency. But sooner or later you’ll need to show users dates and times in their own local time zones. There are a few ways to handle this in SQL Server.

Why Store in UTC?

Storing everything in UTC makes it easy to compare times, especially when data comes from different regions. You don’t have to worry about daylight saving time or other local quirks in your database storage layer.

But presentation is another story. If your application serves users in New York, London, and Tokyo, you’ll need to display those UTC timestamps in the right local times.

The AT TIME ZONE Approach

Starting with SQL Server 2016, Microsoft introduced the AT TIME ZONE function, which is by far the easiest and most reliable way to handle time zone conversions.

Here’s an example:

DECLARE @UtcDateTime DATETIME = '2025-10-03T14:00:00Z';

SELECT
    @UtcDateTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS LocalTime;

Output:

LocalTime                                    
---------------------------------------------
               2025-10-03 10:00:00.000 -04:00

What’s happening here:

  • First, AT TIME ZONE 'UTC' tells SQL Server that the given datetime is in UTC.
  • Then, chaining another AT TIME ZONE converts it into the desired time zone (in this case, Eastern Standard Time).

This method is smart enough to handle daylight saving time shifts automatically, which is a big win compared to manual offset calculations.

Time Zone Names

The trickiest part is knowing the exact time zone name SQL Server expects. These are the same as Windows time zone identifiers, such as:

  • Pacific Standard Time
  • Central European Standard Time
  • India Standard Time
  • Tokyo Standard Time

If you try to use abbreviations like PST or EST, they won’t work. You’ll need the full Windows time zone name. Fortunately, SQL Server 2016 introduced a handy view called sys.time_zone_info, which lists all the time zones recognized by your server:

SELECT * 
FROM sys.time_zone_info;

What About Older Versions of SQL Server?

If you’re on a version older than 2016, you don’t have AT TIME ZONE. A common (but fragile) workaround is to manually add or subtract a fixed offset using DATEADD(): 

DECLARE @UtcDateTime DATETIME = '2025-10-03T14:00:00';

-- Simulate server being UTC-5
SELECT DATEADD(HOUR, -5, @UtcDateTime) AS LocalTime;

But the problem with this approach is that it’ll break whenever daylight saving time changes.

A better approach is to calculate the offset dynamically using the server’s system clock:

DECLARE @UtcDateTime DATETIME = '2025-10-03T14:00:00Z';

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), @UtcDateTime)
AS LocalTime;

Here’s how it works:

This method is safer because:

  • It avoids hardcoding an offset.
  • It respects the server’s current local time zone (including daylight saving changes).
  • It avoids non-atomic date subtraction operations that can cause rare race conditions.

The main limitation is that this only converts UTC to the server’s local time zone. If your users are spread across multiple time zones, you’ll need to handle conversion in the application layer or upgrade to SQL Server 2016+.

Best Practices

Here are some best practices and other things to consider when it comes to working with dates and timestamps in SQL Server:

  • Always store timestamps in UTC.
  • Convert to local time only when displaying data to the end user.
  • Use AT TIME ZONE whenever possible – it’s accurate and much simpler than manual calculations.
  • On pre-2016 SQL Server, use the DATEDIFF()/DATEADD() pattern instead of fixed offsets.
  • If your users are in different time zones, plan to convert in the application layer or move to a modern SQL Server version.

Summary

Converting UTC to local time in SQL Server isn’t difficult when you know how. If you’re on SQL Server 2016 or later, AT TIME ZONE should be your go-to function. It saves you from messy offset logic and makes your queries more reliable. For older versions, the DATEDIFF()/DATEADD() method can work well, though it does have its limitations.

By keeping everything in UTC at the database level and converting only at presentation time, you’ll avoid a lot of headaches and make your application more consistent across different regions.