How to get timezone offset from the timezone name

user_6607787 0
Viewed 4646

I have a timezone name like America/Vancouver saved in a SQL Server database. I want to get the UTC offset from the timezone name in SQL like America/Vancouver has -08:00 offset. So how can I write a query in SQL? Your help is much appreciated

timezone-offset timezone sql-server-2005 sql-server-2008 sql-server
2 Answers

Use DATEPART with TZ parameter. Example :

SELECT DATEPART(tz, (CAST('2021-01-01' AS DATETIMEOFFSET) AT TIME ZONE 'Central European Standard Time'));

The result is in minutes.

user_126598720

Unfortunately SQL Server doesn't yet support IANA time zone identifiers (like America/Vancouver) directly. For now, the best option is to convert from the IANA identifier to a corresponding Windows time zone identifier in your application layer, and store that in your SQL Server.

For example, if you are running .NET in your application layer you can use my TimeZoneConverter project:

string tz = TZConvert.IanaToWindows("America/Vancouver");
// Result:  "Pacific Standard Time"

Then you can use the AT TIME ZONE function in your SQL Server code:

SELECT sysdatetimeoffset() AT TIME ZONE 'Pacific Standard Time'

The above will give you the current date, time, and offset in the given time zone, which you can use as the basis for your query against the user's availability (mentioned in the question comments).

Keep in mind that the offset adjustment will be different depending on whether daylight saving time is in effect or not. Despite the word "Standard" in the Windows identifier, DST is indeed taken into account where applicable.

Alternatively, if for some reason you must do this entirely in SQL Server without converting to Windows time zones, then you will need to rely on projects such as my SqlServerTimeZoneSupport project. That one is a bit old and not very well maintained, so I recommend against that approach if you can help it.

user_6348240
Related