TViJe0WB

1if object_id('tempdb..#tz_offset') is not null drop table #tz_offset
2create table #tz_offset (
3    id int identity(1,1) not null constraint pk_#tz_offset primary key clustered
4    ,the_year int not null
5    ,str_year char(4) not null
6    ,start_date datetime not null
7    ,end_date datetime not null
8    ,dst bit not null
9    ,us_eastern_offset varchar(6) not null
10)
11
12-- if you use years prior to 1986, you will need to add new logic
13drop table if exists #years
14;with years as (
15    select 1986 as the_year
16    union all
17    select the_year + 1
18    from years
19    where the_year < 2132
20)
21select the_year, cast(the_year as char(4)) as str_year
22into #years
23from years
24option(maxrecursion 0)
25
26insert into #tz_offset (the_year, str_year, start_date, end_date, dst, us_eastern_offset)
27select the_year
28     , str_year
29     , str_year + '-01-01' as start_date
30     , case
31           -- 2007-Present: DST begins 2nd Sunday in March
32           when the_year >= 2007
33           then dateadd(day, ((7 - datepart(weekday, str_year + '-03-08')) + 1) % 7, str_year + '-03-08 02:00')
34           -- 1986-2006: DST began first Sunday in April
35           else dateadd(day, ((7 - datepart(weekday, str_year + '-04-01')) + 1) % 7, str_year + '-04-01 02:00')
36       end as end_date
37     , 0 as dst
38     , '-05:00' as us_eastern_offset
39from #years y
40order by y.the_year
41
42insert into #tz_offset (the_year, str_year, start_date, end_date, dst, us_eastern_offset)
43select the_year
44     , str_year
45     , end_date as start_date
46     , case
47           -- 2007-Present: DST ends 1st Sunday in November
48           when the_year >= 2007
49           then dateadd(day, ((7 - datepart(weekday, str_year + '-11-01')) + 1) % 7, str_year + '-11-01 02:00')
50           -- 1986-2006: DST ended last Sunday in October
51           else dateadd(day, ((7 - datepart(weekday, str_year + '-10-25')) + 1) % 7, str_year + '-10-25 02:00')
52       end as end_date
53     , 1 as dst
54     , '-04:00' as us_eastern_offset
55from #tz_offset
56
57insert into #tz_offset (the_year, str_year, start_date, end_date, dst, us_eastern_offset)
58select the_year
59     , str_year
60     , end_date as start_date
61     , cast(the_year + 1 as varchar(4)) + '-01-01' end_date
62     , 0 as dst
63     , '-05:00' as us_eastern_offset
64from #tz_offset
65where dst = 1
66
67select *
68from #tz_offset
69order by start_date