if object_id('tempdb..#tz_offset') is not null drop table #tz_offset
create table #tz_offset (
    id int identity(1,1) not null constraint pk_#tz_offset primary key clustered
    ,the_year int not null
    ,str_year char(4) not null
    ,start_date datetime not null
    ,end_date datetime not null
    ,dst bit not null
    ,us_eastern_offset varchar(6) not null
)

-- if you use years prior to 1986, you will need to add new logic
drop table if exists #years
;with years as (
    select 1986 as the_year
    union all
    select the_year + 1
    from years
    where the_year < 2132
)
select the_year, cast(the_year as char(4)) as str_year
into #years
from years
option(maxrecursion 0)

insert into #tz_offset (the_year, str_year, start_date, end_date, dst, us_eastern_offset)
select the_year
     , str_year
     , str_year + '-01-01' as start_date
     , case
           -- 2007-Present: DST begins 2nd Sunday in March
           when the_year >= 2007
           then dateadd(day, ((7 - datepart(weekday, str_year + '-03-08')) + 1) % 7, str_year + '-03-08 02:00')
           -- 1986-2006: DST began first Sunday in April
           else dateadd(day, ((7 - datepart(weekday, str_year + '-04-01')) + 1) % 7, str_year + '-04-01 02:00')
       end as end_date
     , 0 as dst
     , '-05:00' as us_eastern_offset
from #years y
order by y.the_year

insert into #tz_offset (the_year, str_year, start_date, end_date, dst, us_eastern_offset)
select the_year
     , str_year
     , end_date as start_date
     , case
           -- 2007-Present: DST ends 1st Sunday in November
           when the_year >= 2007
           then dateadd(day, ((7 - datepart(weekday, str_year + '-11-01')) + 1) % 7, str_year + '-11-01 02:00')
           -- 1986-2006: DST ended last Sunday in October
           else dateadd(day, ((7 - datepart(weekday, str_year + '-10-25')) + 1) % 7, str_year + '-10-25 02:00')
       end as end_date
     , 1 as dst
     , '-04:00' as us_eastern_offset
from #tz_offset

insert into #tz_offset (the_year, str_year, start_date, end_date, dst, us_eastern_offset)
select the_year
     , str_year
     , end_date as start_date
     , cast(the_year + 1 as varchar(4)) + '-01-01' end_date
     , 0 as dst
     , '-05:00' as us_eastern_offset
from #tz_offset
where dst = 1

select *
from #tz_offset
order by start_date