SQL Server Jobs / Schedules - US vs. UK Daylight Savings Adjustment

Question

SQL Server Jobs / Schedules - US vs. UK Daylight Savings Adjustment

We have a UK based server which needs to run a SQL Agent Job at 16:30 (US Central time – this may seem strange but it because of the availability of some data). Usually this wouldn’t be a problem because the time difference between the UK and US is 6 hours so we schedule our job for 22:30.

But because the UK and US adjust their clocks at different times in March and November for daylight savings, there is a 2 week period when the time difference between the UK and US is 5 hours.

I would like to implement a way in which SQL Server could identify this change of time difference and then re-schedule the job it’s self using something like sp_update_schedule.

One approach I have is to get the current timestamp on a US based server and then compare that with the current UK time. If the time difference is 5 hours then reschedule the job to 21:30, if the time difference is 6 hours then reschedule the job to 22:30.

But can anyway suggest another solution, perhaps one which wouldn’t involve get the current timestamp on the US based server and ideally not having to store a list of daylight savings adjustment dates in a table. Something more dynamic maybe?

Answer 1

Actually, it looks like you've mentioned the two viable solutions: Ask a server on that timezone or store a list of critical dates. The party line as to why at the first link below. You can implement your own function (which would have to either store a list of dates or ask a server for the offset). Or you can get a public domain list of time zone information (as at the second link below). There are also several web services which will return offsets. Oracle implements that database of time zones to convert between named time zones, so if you already support both you could do that.

There is a C# implementation that you could wrap in CLR functions at the third link.

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

http://www.twinsun.com/tz/tz-link.htm

http://timezone.codeplex.com/