Determining remote daylight saving in sql server

Question

I want to determine the if daylight saving time is active or not, but in a region different to where my server is located.

My problem is I want to check the daylight saving of London and my server is in Canada; is it possible to find the daylight saving of a different time zone?

Answer 1

The best thing to do would be to use one of the two common Time Zone databases.

However, it appears that (according to this question) it is difficult to use the Microsoft TimeZone database from SQL Server because the TimeZoneInfo class is marked with a [HostProtection] attribute with MayLeakOnAbort set true.

I believe a possible solution would be to use NodaTime classes instead. These should be accessible from SQL CLR, and provide access to both databases.

I will try this out and update here when complete.

UPDATE

After fighting with security restrictions of SQL CLR, I've concluded that this is approach is not currently possible either.

My current recommendation would be to do timezone conversions outside of the database, in your application logic.

Answer 2

Here’s a crude IsDST for SQL Server…

CAST((DATEPART(month, DATEADD(week, -1, <DateTime>)) + 2) % 13 / 5 AS bit) AS IsDST

Yields 0 for Standard Time Nov 8 – Mar 7, and 1 for Daylight Time Mar 8 – Nov 7.

Answer 3

You need to deploy a table of DST and look up the DST time for the region you want. DST are published by various organizations and refreshed periodically. What you need to understand is that DST cannot be determined by an algorithm, it can only be looked up as is set by various legislative bodies for various regions, and changes frequently. For example here is the current 2013 DST table. Maintaining your application's DST look-up table current would be a periodic task for your application.

Answer 4

On Sql Server 2016:

With sys.time_zone_info now you can query if a specific timezone is currently on DST.

select * from sys.time_zone_info

Here you have an example result

name                    current_utc_offset    is_currently_dst
Aleutian Standard Time  -09:00                1
Hawaiian Standard Time  -10:00                0
Marquesas Standard Time -09:30                0
Alaskan Standard Time   -08:00                1