· 8 years ago · Nov 30, 2017, 04:58 PM
1-- set the date range
2declare @start_date datetime = datefromparts(year(getdate()), 1, 1)
3declare @end_date datetime = dateadd(year, 1, @start_date)
4
5-- make a table of datetimes with every hour between the dates
6drop table if exists #dates
7;with numbers as (
8 select 0 as num
9 union all
10 select num + 1
11 from numbers
12 where num + 1 < datediff(hour, @start_date, @end_date)
13)
14select n.num + 1 as id
15 , dateadd(hour, n.num, @start_date) as the_date
16into #dates
17from numbers n
18where dateadd(hour, n.num, @start_date) < @end_date
19option(maxrecursion 0)
20
21-- SQL Server 2016+
22select d.id
23 , d.the_date as orig_date
24 -- treat classic datetime as Eastern
25 , d.the_date at time zone 'Eastern Standard Time' as eastern_date
26 , switchoffset(d.the_date at time zone 'Eastern Standard Time', '+00:00') as eastern_to_utc_date
27 -- treat classic datetime as UTC
28 , d.the_date at time zone 'UTC' as utc_date
29 , d.the_date at time zone 'UTC' at time zone 'Eastern Standard Time' as utc_to_eastern_date
30from #dates d
31
32-------------------------------------------------------------------------------
33-- SQL Server prior to 2016
34-- Have to make a timezone and dst offset table
35if object_id('tempdb..#tz') is not null drop table #tz
36create table #tz (
37 id int identity(1,1) not null constraint pk_#tz primary key clustered
38 ,timezone_name nvarchar(100) not null
39 ,standard_offset nvarchar(6) not null
40 ,dst_offset nvarchar(6) not null
41 ,observes_dst bit not null
42)
43insert into #tz (timezone_name, standard_offset, dst_offset, observes_dst)
44values ('UTC', '+00:00', '+00:00', 0)
45 ,('Eastern Standard Time', '-05:00', '-04:00', 1)
46 ,('Central Standard Time', '-06:00', '-05:00', 1)
47 ,('Mountain Standard Time', '-07:00', '-06:00', 1)
48 ,('Pacific Standard Time', '-08:00', '-07:00', 1)
49 ,('Alaskan Standard Time', '-09:00', '-08:00', 1)
50 ,('Aleutian Standard Time', '-10:00', '-09:00', 1)
51 ,('US Eastern Standard Time', '-05:00', '-05:00', 0)
52 ,('US Mountain Standard Time', '-07:00', '-07:00', 0)
53 ,('Hawaiian Standard Time', '-10:00', '-10:00', 0)
54
55if object_id('tempdb..#dst') is not null drop table #dst
56create table #dst (
57 id int identity(1,1) not null constraint pk_#dst primary key clustered
58 ,the_year int not null
59 ,start_date datetime not null
60 ,end_date datetime not null
61 ,dst bit not null
62)
63
64-- if you use years prior to 1986, you will need to add new logic
65;with years as (
66 select 1986 as the_year
67 union all
68 select the_year + 1
69 from years
70 where the_year + 1 <= year(dateadd(year, 10, getdate()))
71)
72insert into #dst (the_year, start_date, end_date, dst)
73select the_year
74 , cast(the_year as varchar(4)) + '-01-01' as start_date
75 , case
76 -- 2007-Present: DST begins 2nd Sunday in March
77 when the_year >= 2007
78 then dateadd(day, ((7 - datepart(weekday, cast(the_year as varchar(4)) + '-03-08')) + 1) % 7, cast(the_year as varchar(4)) + '-03-08 02:00')
79 -- 1986-2006: DST began first Sunday in April
80 else dateadd(day, ((7 - datepart(weekday, cast(the_year as varchar(4)) + '-04-01')) + 1) % 7, cast(the_year as varchar(4)) + '-04-01 02:00')
81 end as end_date
82 , 0 as dst
83from years
84
85insert into #dst (the_year, start_date, end_date, dst)
86select the_year
87 , end_date as start_date
88 , case
89 -- 2007-Present: DST ends 1st Sunday in November
90 when the_year >= 2007
91 then dateadd(day, ((7 - datepart(weekday, cast(the_year as varchar(4)) + '-11-01')) + 1) % 7, cast(the_year as varchar(4)) + '-11-01 02:00')
92 -- 1986-2006: DST ended last Sunday in October
93 else dateadd(day, ((7 - datepart(weekday, cast(the_year as varchar(4)) + '-10-25')) + 1) % 7, cast(the_year as varchar(4)) + '-10-25 02:00')
94 end as end_date
95 , 1 as dst
96from #dst
97
98insert into #dst (the_year, start_date, end_date, dst)
99select the_year
100 , end_date as start_date
101 , cast(the_year + 1 as varchar(4)) + '-01-01' end_date
102 , 0 as dst
103from #dst
104where dst = 1
105
106-- Get timezone results
107select d.id
108 , d.the_date as orig_date
109 -- treat classic datetime as Eastern
110 , todatetimeoffset(d.the_date, case when dst.dst = 1 then tz.dst_offset else tz.standard_offset end) as eastern_date
111 , switchoffset(todatetimeoffset(d.the_date, case when dst.dst = 1 then tz.dst_offset else tz.standard_offset end), '+00:00') as eastern_to_utc_date
112 -- treat classic datetime as UTC
113 , cast(d.the_date as datetimeoffset(3)) as utc_date
114 , switchoffset(cast(d.the_date as datetimeoffset(3)), case when dst.dst = 1 then tz.dst_offset else tz.standard_offset end) as utc_to_eastern_date
115from #dates d
116join #dst dst on dst.start_date <= d.the_date and d.the_date < dst.end_date
117 , #tz tz
118where tz.timezone_name = 'Eastern Standard Time'
119order by 1
120
121-- Get hardcoded timezone results inline without all the setup
122select d.id
123 , d.the_date as orig_date
124 -- treat classic datetime as Eastern
125 ,todatetimeoffset(d.the_date,
126 case when year(d.the_date) >= 2007 then
127 case when d.the_date >=
128 -- second sunday in march (2-3 AM never happens, but we don't check for this value)
129 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-03-08')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-03-08 02:00')
130 and d.the_date <
131 -- first sunday in november (1-2 AM happens twice... data is lost... assume it's the second)
132 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-11-01')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-11-01 02:00')
133 then '-04:00' -- Eastern daylight
134 else '-05:00' -- Eastern standard
135 end
136 else case when d.the_date >=
137 -- first sunday in april (2-3 AM never happens, but we don't check for this value)
138 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-03-08')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-03-08 02:00')
139 and d.the_date <
140 -- last sunday in october (1-2 AM happens twice... data is lost... assume it's the second)
141 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-11-01')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-11-01 02:00')
142 then '-04:00' -- Eastern daylight
143 else '-05:00' -- Eastern standard
144 end
145 end) as eastern_date
146 ,switchoffset(todatetimeoffset(d.the_date,
147 case when year(d.the_date) >= 2007 then
148 case when d.the_date >=
149 -- second sunday in march (2-3 AM never happens, but we don't check for this value)
150 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-03-08')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-03-08 02:00')
151 and d.the_date <
152 -- first sunday in november (1-2 AM happens twice... data is lost... assume it's the second)
153 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-11-01')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-11-01 02:00')
154 then '-04:00' -- Eastern daylight
155 else '-05:00' -- Eastern standard
156 end
157 else case when d.the_date >=
158 -- first sunday in april (2-3 AM never happens, but we don't check for this value)
159 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-03-08')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-03-08 02:00')
160 and d.the_date <
161 -- last sunday in october (1-2 AM happens twice... data is lost... assume it's the second)
162 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-11-01')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-11-01 02:00')
163 then '-04:00' -- Eastern daylight
164 else '-05:00' -- Eastern standard
165 end
166 end), '+00:00') as eastern_to_utc_date
167 -- treat classic datetime as UTC
168 , cast(d.the_date as datetimeoffset(3)) as utc_date
169 , switchoffset(cast(d.the_date as datetimeoffset(3)),
170 case when year(d.the_date) >= 2007 then
171 case when d.the_date >=
172 -- second sunday in march (2-3 AM never happens, but we don't check for this value)
173 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-03-08')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-03-08 02:00')
174 and d.the_date <
175 -- first sunday in november (1-2 AM happens twice... data is lost... assume it's the second)
176 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-11-01')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-11-01 02:00')
177 then '-04:00' -- Eastern daylight
178 else '-05:00' -- Eastern standard
179 end
180 else case when d.the_date >=
181 -- first sunday in april (2-3 AM never happens, but we don't check for this value)
182 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-03-08')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-03-08 02:00')
183 and d.the_date <
184 -- last sunday in october (1-2 AM happens twice... data is lost... assume it's the second)
185 dateadd(day, ((7 - datepart(weekday, cast(year(d.the_date) as varchar(4)) + '-11-01')) + 1) % 7, cast(year(d.the_date) as varchar(4)) + '-11-01 02:00')
186 then '-04:00' -- Eastern daylight
187 else '-05:00' -- Eastern standard
188 end
189 end) as utc_to_eastern_date
190from #dates d
191order by 1