5

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?

Pedro del Sol
  • 2,840
  • 9
  • 39
  • 52
Rohit Vyas
  • 1,949
  • 3
  • 19
  • 28
  • 1
    refer duplicate (no answer): http://stackoverflow.com/questions/6781121/determine-if-daylight-saving-time-is-active-sql-server. The short answer is that there is nothing built in. Windows processes DST using "lookup tables" stored in the registry which are not normally accessible from T-SQL. – RichardTheKiwi Apr 26 '13 at 08:29

3 Answers3

5

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Deploying a table of time zone data in your database is not usually a good idea. It will end up being an artifact that you have to maintain manually, and over time you will find it to be inaccurate. – Matt Johnson-Pint Apr 26 '13 at 16:07
  • 3
    I stand by my recommendation – Remus Rusanu Apr 27 '13 at 12:33
  • @RemusRusanu Do you know where we can get the DST SQL script for a specific year like 2015? – sqluser Jul 10 '15 at 04:01
  • Things that change arbitrarily require maintenance. It's just a matter of who does the maintenance. If you can find a reliable source, I've found that importing it regularly is the lowest maintenance option other than querying an external source in real time. It's not really any different than postal code data. – bielawski Jun 07 '17 at 15:00
1

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
bruno.bologna
  • 475
  • 4
  • 14
  • 1
    I'm not sure why this was down-voted. The OP didn't ask for a way to know DST for an arbitrary time - which this can't provide. Perhaps the data source isn't reliable???? It would be nice to know. On its face the solution looks viable. – bielawski Jun 07 '17 at 15:07
-1

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.

Mike R
  • 9
  • 2