Daylight savings

Anyone if SQL server has any built-in mechanism for handling Daylight savings?
Mostly for figuring out time passed between two datetime values...
Thanks in advance.Anyone if SQL server has any built-in mechanism for handling Daylight savings?

No, since it's a regional thing anyway

Mostly for figuring out time passed between two datetime values...

Yes...DATEDIFF

What are you trying to do?|||well, like you said I use DATEDIFF to get the amount of time passed between two dates...but (where I live daylight time shifts backwards at 2 (to 1 am) am last sunday of october, and forward at 2 am (to 3 am) on first sunday of april.

basically I want to count time properly...

DATEDIFF(Minute, '2004-04-04 1:30', '2004-04-04 3:30') gives 120 but in reality only 60 minutes passed between the first and second time.|||well, like you said I use DATEDIFF to get the amount of time passed between two dates...but (where I live daylight time shifts backwards at 2 (to 1 am) am last sunday of october, and forward at 2 am (to 3 am) on first sunday of april.

basically I want to count time properly...

DATEDIFF(Minute, '2004-04-04 1:30', '2004-04-04 3:30') gives 120 but in reality only 60 minutes passed between the first and second time.|||You need to build a table that holds (perhaps by region) the dates and times that the switch occurs.

There are some places in the states (by county level even) where the switch does not occur.

Anyway. If the dates EXISTS in the range, then you need to handle it accordingly.

Most likely with a CASE Statement|||If both of the DATETIME values are from the same locale as the server, you could use the GetUTCDate() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_4kkp.asp) to convert them both to UTC, then take the DateDiff() of the UTC DATETIME values. If the DATETIME values are from different locales, let me know how you do it!

-PatP|||It say GetUTCDate() Requires 0 parameters...|||GetUTCDate() works just like GetDate(), but expresses the date/time returned as UTC (also known as Greenwich or Z time) instead of local time. If your app stores server based times in UTC (which is effectively mandated if you have servers in more than one timezone), then life is simple. If you have times stored based on local time, then things get to be really interesting!

Oh yeah, I forgot to mention, there isn't any way to dependably convert a stored local time to Z time, although you can convert Z time to local time if you know which local time.

-PatP