Recently a topic came up at work, and I remember seeing it on Reddit, so you know, that definitely is a sign that I had to write a blog on it.
For a lot of people, we typically standardize on a single time zone for our life, and in turn our data. Maybe it is the time zone of our headquarters, our house, and sometimes it is the standard time zone UTC. It is often suggested that every organization should use UTC, and that is a great suggestion for many things.
However…if you store all your times as UTC, this becomes a nightmare for some kinds of reporting. Like sales for “yesterday.” While UTC may work for places like the UK (even though they also have Summer Time to deal with there), it does not work in the US for many 24 hours shops, because a day is generally from 12am-12am local time, not 7pm-5pm. So you have to find some way to convert in a way that makes these sorts of queries straightforward.
In this blog I will show you how AT TIME ZONEworks, show you how to find the time zone list, and give you a few more details about dealing with time zones. Not all of it makes sense on how it is implemented, but it is a very handy bit of code to have around.
Note, for simplicity a lot of the examples will use system functions that would give you the answer already. Like if I need the UTC time now, you can use
SYSUTCDATETIME(), but in most cases, you can replace these system function calls with columns or variables.
The basics
SQL Server had a number of date and time day types and functions and I just want to talk about a few in this blog.
I will assume you know about most of the date types and functions that are unrelated to changing a time zone already, but let’s talk about one: datetimeoffset.
This data type is very similar to a datetime2, but it has an offset, which is the number of hours away from UTC, which is the base time zone with an offset of 0.
For example, there is a SYSDATETIMEOFFSET() function that you can fetch the current date and time on your server:
SELECT SYSDATETIMEOFFSET();
This returns something like the following:
---------------------------------- 2025-08-22 13:34:04.2319844 -04:00
In addition to the regular date and time value, you can see the offset, which is: -04:00 or negative four hours from UTC.This means at 00:00 or 12am in UTC, it is 20:00 or 8pm in my time zone right now.
There is no SYSUTCDATETIMEOFFSET, but one simple method to get this is to use the SYSDATETIME() function and add the offset to it. There are a few methods of doing this:
TODATETIMEOFFSET()
If you know the offset you want to add, you can just add it directly:
--four hours, but the parameter is in minutes. SELECT TODATETIMEOFFSET(getdate(),'-04:00'); -- you can also supply this as an integer using -- -6 * 40 or -240 minutes if you wish
Note that the output is in the following format:
---------------------------------- 2025-08-24 14:52:50.217 -04:00
SWITCHOFFSET()
If you want to change the value of a datetimeoffset value, you can do that using SWITCHOFFSET. This converts the time from its current offset, to the new offset, but the output is the same point in time. For example:
DECLARE @baseTime datetime2(0) = getdate();
SELECT switchoffset(@baseTime,'-0:00');
DECLARE @datetimeOffset datetimeoffset =
TODATETIMEOFFSET(@basetime,'-04:00');
SELECT @baseTime as baseTime;
SELECT @dateTimeOffset as [baseTimeWith-4:00Offset];
SELECT SWITCHOFFSET (@datetimeOffset,'-08:00') as [ChangedTo-8:00];
--Offsets here can also be denoted in minutes, as an integer,
--from UTC as well.
The output will look like this:
baseTime --------------------------- 2025-09-07 13:26:04 baseTimeWith-4:00Offset ---------------------------------- 2025-09-07 13:26:04.0000000 -04:00 ChangedTo-8:00 ---------------------------------- 2025-09-07 09:26:04.0000000 -08:00
You can see the offset was simply added with the TODATETIMEOFFSET(@basetime,’-04:00′). Then in the last two rows output, the diference in offset is 4 hours, so the values in the date and time parts are 4 hours apart: 15:03 in one, and 11:03 in the other. This is because while TODATETIMEOFFSET simply adds the the offset, but SWITCHOFFSET,shifts the offset of a datetimeoffset value (TODATETIMEOFFSET will do this as well, but SWITCHOFFSET will fail with a runtime error if you pass it a value without an offest already:
SELECT SWITCHOFFSET('2025-01-01','-4:00');
This gives you the following runtime error:
---------------------------------- Msg 9812, Level 16, State 0, Line 90 The timezone provided to builtin function switchoffset is invalid.
Demonstrating AT TIME ZONE
The method I am more apt to use involves AT TIME ZONE. This is one of those oddly formed functions that doesn’t have a conventional parameter structure, but is a function nevertheless.
It works like this:
SELECT 'datevalue' AT TIME ZONE 'timezone value';
Note that, as you have seen, the offset doesn’t really represent a time zone, but rather the offset from UTC. the time zone information will simply fetch the offset for that point in time, for a time zone. The offset will not honor daylight saving time if you add time to it, nor will you be able to tell the difference between the Eastern Standard Time, Haiti Standard Time or Cuba Standard Time after they have been applied as they all currently have a -4:00 offset.
So say you have a time value like you get from SYSDATETIME(). you could add the time zone information for where I live in the Eastern Time Zone in the US using:
SELECT SYSDATETIME() AT TIME ZONE 'Eastern Standard Time';
Sitting here on this Sunday afternoon editing this post, it is 13:57 (or 1:57PM for most of us Americans), and in Daylight Saving Time it is currently -4 hours from UTC -0. So, this returned:
---------------------------------- 2025-09-07 13:57:04.8375379 -04:00
A later section of the blog will cover the interesting nature of finding time zone values.
Converting a value with a time zone to another
If you have a value that is already a DATETIMEOFFSET value, you can convert easily. Like a value from Central Standard Time:
SELECT SYSDATETIME() AT TIME ZONE 'Central Standard Time';
Which just returned:
---------------------------------- 2025-09-07 14:00:28.7438900 -05:00
Take that value and just use AT TIME ZONE:
SELECT CAST('2025-09-07 14:00:28.7438900 -05:00'
AS datetimeoffset) AT TIME ZONE 'Eastern Standard Time';
We are one hour different, as you can see.
----------------------------------- 2025-09-07 15:00:28.7438900 -04:00
Note that I had to cast that date value, rather than using a string. Th
SELECT '2025-09-07 14:00:28.7438900 -05:00'
AT TIME ZONE 'Eastern Standard Time';
This returns:
Msg 8116, Level 16, State 1, Line 159 Argument data type varchar is invalid for argument 1 of AT TIME ZONE function.
Converting a value without an expiict time zone
For this feat, we generally will want to use something like this, which is very similar to what we just loooked at in the previous section, but here I am putting together two calls to AT TIME ZONE:
SELECT 'datevalue' AT TIME ZONE 'the assumed time zone'
AT TIME ZONE 'time zone going to';
A common use of this is to convert to and from UTC and vice versa. So for me to convert to UTC from my local time zonethis way:
SELECT SYSDATETIME() AT TIME ZONE 'Eastern Standard Time'
AT TIME ZONE 'UTC';
Which today returned:
----------------------------------- 2025-09-07 18:13:05.5681561 +00:00
These are the individual steps:
SELECT SYSDATETIME() AS local_time;
SELECT SYSDATETIME() AT time zone
'Eastern Standard Time' as_local_with_offset;
SELECT SYSDATETIME() AT time zone
'Eastern Standard Time' AT time zone 'UTC' AS utc_with_offset;
SELECT CAST(SYSDATETIME() at time zone
'Eastern Standard Time' AT time zone 'UTC' AS datetime2(7))
AS utc_in_datetime2;
--just to show that the output's match:
SELECT SYSUTCDATETIME() as check_target_output;
The output:
as_local_with_offset ---------------------------------- 2025-09-07 14:18:22.0429603 -04:00 utc_with_offset ---------------------------------- 2025-09-07 18:18:22.0429603 +00:00 utc_in_datetime2 --------------------------- 2025-09-07 18:18:22.0429603 check_target_output --------------------------- 2025-09-07 18:18:22.0429603
The last two match, so you can see that your value is what you expected (not with an offset, or you could use the utc_with_offset value.)
If the value you have is UTC and you want to translate that o your timezone.
SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC'
AT TIME ZONE 'Eastern Standard Time';
Which returned:
---------------------------------- 2025-09-07 14:22:33.2463017 -04:00
And of course, you can then cast that to a different datatype if you don’t need the time zone information too.
SELECT CAST(SYSUTCDATETIME() AT TIME ZONE 'UTC'
AT TIME ZONE 'Eastern Standard Time' AS datetime2(0));
Which returned:
--------------------------- 2025-09-07 14:25:09
Note: again, if you simply want local time with offset, use
SYSUTCDATETIME(), this technique is more typically used with values other than the current time.
Finding timezones, included your server’s
So far, so simple. You can manipulate time values, and you can convert from one time zone’s current offsets to another. This however is where things get a bit more tricky.
List all of the timezones
As a first step, this is how you get all of the timezones.
SELECT * FROM sys.time_zone_info;
There are 141 entries in the list, so it isn’t as simple as just looking through the 24 “natural” time zones. So how do you find your own time zone? Well, we could get our current offset from:
SELECT SYSDATETIMEOFFSET(),
DATENAME(TZOFFSET, SYSDATETIMEOFFSET());
--datepart would get the # of minutes as int
This returns:
---------------------------------- ------------------------------ 2025-09-07 14:39:40.0631545 -04:00 -04:00
So you can start to narrow it down using:
SELECT * FROM sys.time_zone_info WHERE current_utc_offset = DATENAME(TZOFFSET, SYSDATETIMEOFFSET());
For me, this narrowed to down to a long list, though certainly better than 141:
name current_utc_offset is_currently_dst --------------------------------- ------------------ ---------------- Eastern Standard Time -04:00 1 Haiti Standard Time -04:00 1 Cuba Standard Time -04:00 1 US Eastern Standard Time -04:00 1 Turks And Caicos Standard Time -04:00 1 Venezuela Standard Time -04:00 0 Central Brazilian Standard Time -04:00 0 SA Western Standard Time -04:00 0
There are two that seems to match: 'Eastern Standard Time' and 'US Eastern Standard Time'. Lets see what we can figure out in the next section:
Find your timezone
To find your time zone, there is a function you can call:
SELECT CURRENT_TIMEZONE();
This returned for me:
------------------------------------------ (UTC-05:00) Eastern Time (US & Canada)
Um… what? This is maybe a bit confusing because it doesn’t match anything in the list, and not just because this says UTC-5:00… But as I have shown in the previous section (and throughout the blog!), we are currently in UTC-4:00 due to Daylight Saving Time.
And of course:
SELECT * FROM sys.time_zone_info WHERE name = CURRENT_TIMEZONE();
Would be too easy, and even if we narrow it down to one’s with ‘Eastern’ in them:
SELECT * FROM sys.time_zone_info WHERE name like '%Eastern%';
That returns these rows, and also quite a few rows (not unlike when we queried by offset):
name current_utc_offset is_currently_dst ------------------------------- ------------------ ---------------- Eastern Standard Time (Mexico) -05:00 0 Eastern Standard Time -04:00 1 US Eastern Standard Time -04:00 1 SA Eastern Standard Time -03:00 0 AUS Eastern Standard Time +10:00 0
So there is an Eastern Standard Time, an US Eastern Standard Time, a SA Eastern Standard time (South Africa, which my messed up mind really expected it to be closer to UTC and farther from US than 2 time zones), and AUS Eastern Standard Time. Clear?
Of course it isn’t.
But seeing that there is on that has a prefix of ‘Us’, that seems like the one to use. So, there should be all the US Time Zones prefixed with US, right?
SELECT name, is_currently_dst FROM sys.time_zone_info WHERE name like 'US%'
Sure 🙂
name ------------------------------- US Mountain Standard Time US Eastern Standard Time
One thing I found (using Google’s AI Overview) that did work, but required the use of xp_regread (an undocumented stored procedure that reads the registry), was:
DECLARE @TimeZone VARCHAR(50); EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName',@TimeZone OUT; SELECT @TimeZone AS TimeZone;
For me, this returned:
TimeZone ------------------------ Eastern Standard Time
Honestly, the best thing to do is to check that registry key, of just find the one that looks the most like your time zone and test to make sure it works like you expect. It sadly does not tell us the rules for when the time zone changes.
Finding your daylight saving time changes
This is a question that often comes up, and is likely something you might add to your calendar table in your local time zone. But sometimes you want to know when it changes for other people (like if you work with people in another country that have different time change algorithms).
My first attempt at this process was to look for the case where the DATEDIFF between two dates was <> 24 hours. Sadly, neither DATEADD nor DATEDIFF honor daylight saving time changes. So instead I changed it to do something slightly different and checked when the offset changed:
DECLARE @start_date datetime2 = '2023-01-01',
@number_of_years_to_check int = 5,
@timeZoneName varchar(100) = 'Eastern Standard Time';
With BaseRows AS
(
--create rows for as many years as askse for (used 366 days
--for leap days
SELECT DATEADD(DAY,VALUE,@start_date) fromDay,
DATEADD(DAY,VALUE + 1,@start_date) toDay
FROM GENERATE_SERIES(0,@number_of_years_to_check * 366)
),
RowsToCheck AS (
--calculate the offestes
SELECT fromDay AS DateValue,
DATEPART(TZOFFSET,fromDay AT TIME ZONE 'utc'
AT TIME ZONE @timeZoneName) AS DayOffset,
DATEPART(TZOFFSET,toDay AT TIME ZONE 'utc'
AT TIME ZONE @timeZoneName) AS NextDayOffset
FROM BASEROWS
)
--compare offsets. if they don't match, its a change
SELECT @timeZoneName AS TimeZone,
CAST(DateValue AS DATE) AS ChangeDate,
--if the offet increases, Daylight…
CASE WHEN DayOffset < NextDayOffset
THEN 'Daylight Saving Time'
ELSE 'Standard Time'
END AS ChangeTo
FROM RowsToCheck
WHERE DayOffset NextDayOffset;
This returns the following list of days where the offset changes:
TimeZone ChangeDate ChangeTo ----------------------- ---------- -------------------- Eastern Standard Time 2023-03-12 Daylight Saving Time Eastern Standard Time 2023-11-05 Standard Time Eastern Standard Time 2024-03-10 Daylight Saving Time Eastern Standard Time 2024-11-03 Standard Time Eastern Standard Time 2025-03-09 Daylight Saving Time Eastern Standard Time 2025-11-02 Standard Time Eastern Standard Time 2026-03-08 Daylight Saving Time Eastern Standard Time 2026-11-01 Standard Time Eastern Standard Time 2027-03-14 Daylight Saving Time Eastern Standard Time 2027-11-07 Standard Time
Now, you might think this works well for any time zone, no matter how the rules have changed. In my locations, including the US, we are talking about abolishing the change one day. So anytime they didn’t have daylight saving time, you’d think they wouldn’t have an offset change. But, if you set the start date to ‘0001-01-01’, you will see that back in the year 0001:
TimeZone ChangeDate ChangeTo ----------------------- ---------- -------------------- Eastern Standard Time 0001-01-01 Standard Time Eastern Standard Time 0001-04-01 Daylight Saving Time Eastern Standard Time 0001-10-28 Standard Time Eastern Standard Time 0002-04-07 Daylight Saving Time Eastern Standard Time 0002-10-27 Standard Time Eastern Standard Time 0003-04-06 Daylight Saving Time Eastern Standard Time 0003-10-26 Standard Time Eastern Standard Time 0004-04-04 Daylight Saving Time Eastern Standard Time 0004-10-31 Standard Time Eastern Standard Time 0005-04-03 Daylight Saving Time Eastern Standard Time 0005-10-30 Standard Time
Summary
As we all know, time zones are a pain. This article clearly didn’t change anything there. I hope when you have to go through this pain, what I have shared is helpful. We looked at three main functions that we can use to deal with time zone offsets in date values: AT TIME ZONE, TODATETIMEOFFSET, SWITCHOFFSET. We looked at how you can get the offset using DATEPART and DATENAME using the TZOFFSET setting.
We also showed some of the difficulties in figuring out the what the time zone setting is for your server, and to calculate when your timezone will change from Daylight Saving Time or back to Standard Time, even in the year 0003, because who didn’t need to know the time in New York City long before it existed.
Drsql.link 
Leave a comment