In our system we have a Stored Procedure that creates shifts for users. These shifts have a specified start/end time and a duration. When these shifts cross a DST Boundary, the duration comes out incorrect. Take the following Example:
declare @start DATETIME = '2017-03-11 22:00:00.000',
@end DATETIME = '2017-03-12 06:00:00.000'
select (DATEDIFF(hh, @start, @end)) as 'Elapsed Hours'
This returns an elapsed hours of 8 hours. However, on 3/12 of this year, DST begins and clocks move an hour forward. So the actual elapsed hours for this time period is only 7. I know that in SQL 2016 I can use the AT TIME ZONE function to get around this, but unfortunately I have to support this in SQL 2008 - 2016.
I've found another question that seems to give me an idea: How to create Daylight Savings time Start and End function in SQL Server I could use these functions to get the DST start/end dates then do some calculation to see if the shift crosses one of these boundaries, then apply the appropriate offset but this seems like an ugly option to me. First of all, not all locations observe DST, and not all countries use the same DST Schedules... So I'm thinking there must be a better way to do this. Has anyone else in the community run across this problem and found a better way to handle it?
Because of the uncertainty you've identified in DST schedules at different locations, it is often recommended to do all datetime calculations in UTC and display them in local time for clients. DST rules can change at any time based on local laws, even from city to city that UTC is by far the best way to calculate time.
Edit for additional clarity: Even the SQL 2016 fix will rely on human intervention and rule updates to be keep current as different countries, states, and cities make changes to their DST laws. UTC is the best consistently reliable tool at your disposal.