CODEWITHSUNDEEP
.netsql-servertimezone
DJA
DJA

Reputation: 659

t-sql 'at time zone' does not know all of the timezones that .net knows about

My user interface features a dropdown list of all of the timezones populated by .Net's TimeZoneInfo.GetSystemTimeZones() (in MVC Razor) that is then recorded against the user.

            <select id="TimeZoneAbbreviation" name="TimeZoneAbbreviation">
                @foreach (var tz in TimeZoneInfo.GetSystemTimeZones())
                {
                    <!option value="@tz.StandardName" @(tz.StandardName == Model.Project.TimeZone ? "selected='selected'" : "")>@tz.DisplayName</!option>
                }
            </select>

I have a stored procedure that needs to do some processing and convert a UTC time that something is logged in the database into the current user's local time for presentation on the screen.

In testing, I discovered that a handful (16) timezones cause 'at time zone' to throw an error. These problems occur on both SQL Azure and Sql Server 2016

declare @timezone as varchar(max) = 'Alaskan Standard Time'
select cast(cast(GETUTCDATE() as datetimeoffset) at time zone @timeZone as datetime) -- this works

set @timezone = 'Cabo Verde Standard Time'
select cast(cast(GETUTCDATE() as datetimeoffset) at time zone @timeZone as datetime) -- does not work

set @timezone = 'Coordinated Universal Time'
select cast(cast(GETUTCDATE() as datetimeoffset) at time zone @timeZone as datetime) -- does not work

This results in 

(1 row affected)
Msg 9820, Level 16, State 1, Line 411
The time zone parameter 'Cabo Verde Standard Time' provided to AT TIME ZONE clause is invalid.
Msg 9820, Level 16, State 1, Line 414
The time zone parameter 'Coordinated Universal Time' provided to AT TIME ZONE clause is invalid.

So I discover that SQL has its own list that is different to that of .Net. So the immediate thought to not use .Net to get the list. Rather, I should simply ask SQL Server via a query. 

select * from sys.time_zone_info

The problem with this approach is that this returns a different and incompatible list to that of .Net. In my .Net code I have some code like this

TimeZoneInfo.FromSerializedString(project.TimeZone)

This will break if it uses an unknown string that SQL Server knows about.

Fun fact #1, the documentation at https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017 states that the list is sourced from "KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones". Sure enough, 'Coordinated Universal Time' is not in the list (and it does not work). But 'Cabo Verde Standard Time' IS in the list (but that does not work either).

Fun fact #2, "Cabo Verde Standard" does not work but "Cape Verde Standard Time" does work. These are not typos. These are actual timezones.

(Before someone asks, I converted the GetUTCDate() into a datetimeoffset as the documentation says "If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules." which is what I wanted to happen).

So, the question... what am I to do? What have others done? This whole area appears to be a total dog's breakfast.

I guess my problem is that I have timezone processing both in the stored procs and .Net and their timezone processing is incompatible. I probably should somehow just do one and not the other.

Thanks in advance.

Upvotes: 0

Views: 1647

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131492

The real problem is that nobody uses Windows timezone names. Everyone uses IANA timezone names making the at time zone clause a bit ... unhelpful.

In this case you should probably use the ID, not the DisplayName. For example, the display name Cabo Verde Daylight Time has an ID of Cape Verde Standard Time. The name Coordinated Universal Time has an ID of UTC.

BTW in my case, on SQL Server 2017 on Windows 10, I get 139 timezone names

Update

I forgot to add that Matt Johnson created a SQLCLR UDF that adds support for the IANA timezones through Noda Time. If you're serious about timezones you should probably use it instead of AT TIME ZONE

Upvotes: 3

Related Questions