Sunday, March 25, 2012

Day Light Saving Time Change

According to Microsoft, Day Light saving time changes do not impact SQL
Server, but I want to prove that with some tests. (
http://support.microsoft.com/gp/cp_dst)
I tried using DATEADD and DATEDIFF with time period that covered the spring
forward time for both 4/1/2007 as well as 3/11/2007, they both returned the
wrong result
The following queries return the same time
select dateadd(minute,10,cast('2007-03-11 01:59:00' as datetime))
select dateadd(minute,10,cast('2007-04-01 01:59:00' as datetime))
2007-04-01 02:01:00.000
Is there any other way to verify that DST change has no impact?
Thanks
SQL Server does not ever adjust times for DST changes.
DST events are only relevant for functions that retrieve local system time
(e.g. GETDATE(), CURRENT_TIMESTAMP).
If you are storing data and expect it to follow DST rules, then that is your
responsibility with the new rules, just as it has always been your
responsibility with the old rules.
"Shailesh Khanal" <shailesh.khanal@.timewarner.com> wrote in message
news:uCdpbU6SHHA.3996@.TK2MSFTNGP04.phx.gbl...
> According to Microsoft, Day Light saving time changes do not impact SQL
> Server, but I want to prove that with some tests. (
> http://support.microsoft.com/gp/cp_dst)
> I tried using DATEADD and DATEDIFF with time period that covered the
> spring forward time for both 4/1/2007 as well as 3/11/2007, they both
> returned the wrong result
> The following queries return the same time
> select dateadd(minute,10,cast('2007-03-11 01:59:00' as datetime))
> select dateadd(minute,10,cast('2007-04-01 01:59:00' as datetime))
> 2007-04-01 02:01:00.000
> Is there any other way to verify that DST change has no impact?
> Thanks
>

No comments:

Post a Comment