0

Here is a quick summary of my application's requirement:

  • A user can create different locations - each with a TimeZoneInfo property.
  • Each location can have one or more Execution Windows specified - this is a date/time range during which maintenance processes may be executed.
  • The SQL Server instance which will execute the maintenance task could be on a server in a timezone which is different to all configured locations

I want that the user can specify the Execution Window details using their current timezone and that the database will be intelligent enough to convert it to it's own timezone when checking if it needs to run any maintenance tasks.

I'm thinking that I could save the offset from utc against the location and then use this with the database server's offset from utc to perform time conversions.

However, the location utc offset will only be set when creating the location and, if that location uses daylight savings, the time conversions are likely to be out by an hour for 6 months of the year.

Is there a cleverer way to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
amcdermott
  • 1,565
  • 15
  • 23
  • In particular, see [my answer](http://stackoverflow.com/a/26368158/634824), which addresses your concerns. – Matt Johnson-Pint Oct 10 '15 at 22:30
  • Also - if you are relying on SQL Agent to execute the job at the scheduled time, it's critical that the database server has its time zone set to UTC - as SQL Agent currently only allows tasks to be scheduled in terms of local time. See also [this answer](http://serverfault.com/questions/549737/scheduled-jobs-during-hours-of-autumn-time-change/554761#554761), and my note at the end. – Matt Johnson-Pint Oct 10 '15 at 22:38

0 Answers0