SQL Server Lookup Table for Daylight Savings Time (DST)

Oracle is DST-aware since 9i (2001). SQL Server, on the other hand, still has no built-in DST support in the latest version (SQL Server 2012). A quick-and-dirty trick to steal DST-support from Oracle to SQL Server is to create a lookup table in Oracle and copy it to SQL Server. Here is an example table.

	with utc_tm as (
	select to_timestamp_tz('1970-01-01 00:00:00 +00:00') + numtodsinterval(level - 1, 'hour') as utc_tm
	from dual connect by level <= 1000000
	), utc_lcl as (
	select
	utc_tm,
	cast(utc_tm as timestamp with local time zone) as lcl_tm,
	(cast(utc_tm as date) - cast(cast(utc_tm as timestamp with local time zone) as date))*24 as interval_h
	from utc_tm
	), interval_compare as (
	select
	utc_tm,
	lcl_tm,
	interval_h,
	lag(interval_h) over (order by lcl_tm) as lag_int
	from utc_lcl
	), day_chg as (
	select
	*
	from interval_compare
	where
	lag_int is null or
	interval_h <> lag_int
	)
	select
	utc_tm as utc_from,
	lead(utc_tm) over (order by lcl_tm) - interval '1' second as utc_to,
	interval_h
	from day_chg;

view raw daylight_savings_change.sql hosted with ❤ by GitHub