T-SQL Get Hours Offset from Name of Timezone

Question

In DB in field TimeZoneId I have string values like this:

GMT Standard Time
W. Europe Standard Time
Romance Standard Time
FLE Standard Time

What I need to get int value offset in hours by these string values i.e.

GMT Standard Time       | 0
W. Europe Standard Time | 1
Romance Standard Time   | 1
FLE Standard Time       | 2

I need to use ONLY T-SQL.

I don't think there is such a thing in SQL Server. I think this info is coming from the Windows OS. You will have to either create such a table in your database or write a .Net function to do this job. — Racil Hilan, Oct 02 '15 at 14:19
How are those strings getting into your DB? Find the process that does that and modify it to also add the offset. — Tab Alleman, Oct 02 '15 at 15:17
Also be aware that you need offsets in minutes, not hours as some places have odd numbers of minutes. Currently everywhere seems to be to nearest quarter hour, but who can say if that will always be so? — Ben, Oct 02 '15 at 15:33
And offsets change for DST and other reasons. Do not use offsets to represent time zones. See my answer below. — Matt Johnson-Pint, Oct 02 '15 at 16:26

score 2 · Answer 1 · edited May 23 '17 at 11:51

A few things:

The link you gave is severely outdated, and applies only to Windows Embedded POS 1.1. The more accurate list of Windows time zones can be found by looking in your registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones, or by TimeZoneInfo.GetSystemTimeZones() in .NET, or by calling tzutil /L on the command prompt.
What you asked for is invalid, as a time zone cannot be represented by a single number. Time zones include changes to offsets for daylight saving time, and for historical changes from year to year. See "Time Zone != Offset" in the timezone tag wiki.
Working with time zones is usually best done at the application layer rather than the database. There are only a few scenarios that require it at the DB layer. If you don't absolutely need to do it in the database, then don't.
Unlike other database platforms, SQL Server has no built-in time zone conversion capabilities.
To add time zone conversion capabilities, you will need to maintain tables of time zone data, and write conversions functions to use that data. The data changes often, so it's important to keep it up to date.
Fortunately, I have done most of the work for you. You can use my SQL Server Time Zone Support project. It currently works with IANA / TZDB time zones (such as America/New_York, or Europe/London) rather than Windows time zones, but it is on the to-do list. You can either wait for this feature, or convert to IANA zones if you like.

1 Answers1