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?
ThanksI found this in MS site
http://support.microsoft.com/kb/931975/en-us
According to it, dateadd or other date functions are not timezone sensitive,
so I don't see any way to test this without changing the clock on the
server.
Any ideas?
"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
>|||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
>|||> so I don't see any way to test this without changing the clock on the
> server.
Yes, changing the server time is the only way to test. As Aaron mentioned,
SQL Server gets current time from Windows.
Hope this helps.
Dan Guzman
SQL Server MVP
"Shailesh Khanal" <shailesh.khanal@.timewarner.com> wrote in message
news:eICC5j6SHHA.5100@.TK2MSFTNGP06.phx.gbl...
>I found this in MS site
> http://support.microsoft.com/kb/931975/en-us
> According to it, dateadd or other date functions are not timezone
> sensitive, so I don't see any way to test this without changing the clock
> on the server.
> Any ideas?
>
> "Shailesh Khanal" <shailesh.khanal@.timewarner.com> wrote in message
> news:uCdpbU6SHHA.3996@.TK2MSFTNGP04.phx.gbl...
>sql