So that's what the function will do (and it does still take into account edge cases, for example it returns NULL if you pass in 02:30 and say that was in the Eastern time zone):ДЄND = DATEADD(HOUR,-1,src.Local_DST_Start)ДЄND CONVERT(DATE,src.UTC) < DATEADD(YEAR, 1, trg.) The simplest way to think about this is to convert the source time to UTC, then convert to the target time zone. This time, we'll create a single function that takes in a date/time in any of the supported time zones, and converts it to any of the other supported time zones.

Year TimeZoneID UTC_DST_Start UTC_DST_End Local_DST_Start Local_DST_End Again, as with our smaller calendar table from the previous tip, this table should be in memory all of the time, as long as it is being referenced often enough. This creates a table with 520 rows (65 years across 8 time zones) it weighs in at only 40 KB.

Last Sunday in October ( 7 THEN (7*p) ELSE 0 END,Д¬ASE WHEN tz IN (1,8) THEN utcstart ELSE DATEADD(MINUTE,som+locs,utcstart) END,Д¬ASE WHEN tz IN (1,8) THEN utcend ELSE DATEADD(MINUTE,dom+loce,utcend) END UTC end of DST (start of *following* year for areas that don't observe)Д¬ASE WHEN tz.tz IN (1,8) THEN DATEADD(YEAR, 1, cte.d) +CASE WHEN tz.tz 7 THEN p ELSE 0 END,cte.d)))) CASE WHEN tz.tz 7 THEN 7*p ELSE 0 END,DATEADD(MONTH,tz.cm P = CASE WHEN number 7 THEN p ELSE 0 END,cte.d))+1)%7+tz.cd1 all the years from 2000 through 50 years after the current year: For the North American time zones, the calculations for when DST starts mimic those described earlier for time zones that don't shift for DST (including UTC, obviously), we just pretend that DST lasts all year, but the 'DST offset' is actually the same as the standard offset. I won't go into how many iterations this took to get right the iterations could make for a tip or blog post all on their own, though a lot of the logic that drove this query was explained in the first tip in this series. (8,'Australia Central Standard Time','Australia Central Standard Time',570,570) ДЄs with the previous tip, the most complicated part of this scenario is populating the calendar table. (7,'Greenwich Mean Time','British Summer Time', -60,0),

(6,'Newfoundland Standard Time','Newfoundland Daylight Time', -210,-150), (4,'Mountain Standard Time','Mountain Daylight Time', -420,-360), StandardDescription VARCHAR(64) NOT NULL UNIQUE,ДaylightDescription VARCHAR(64) NOT NULL UNIQUE,

Convert time zones