Daylite saving time problem

Hello
I am using SQL Server 2000, SP4
I am calculating number of hours passed between two dates. Both dates have
time set to 00:00:00. I use datediff function it works ok unless the time
interval I pass includes date when time is changed due to Daylite Saving Tim
e
(DST) issue. Instead of one hour more or one hour less datediff keeps
returning constant number of hours.
Does SQL Server 2000 internally support DST depending on a regional settings
in OS?
Thanks in advance.we don't that feature in SQL Server to my knowledge. You can write a UDF to
do the conversion.
Check out this link
http://www.planet-source-code.com/U...cripts/ShowCode!asp/txtCodeId!9
11/lngWid!5/anyname.htm|||Thanks a lot|||Some ideas here maybe:
http://www.aspfaq.com/2218
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||Oh, and also the calendar table.
http://www.aspfaq.com/2519
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||or how about rather than using getdate() to get the two dates in the
first place, use getutcdate() function?
GETUTCDATE
Returns the datetime value representing the current UTC time (Universal
Time Coordinate or Greenwich Mean Time). The current UTC time is
derived from the current local time and the time zone setting in the
operating system of the computer on which SQL Server is running.
Mel|||> or how about rather than using getdate() to get the two dates in the
> first place, use getutcdate() function?
> GETUTCDATE
> Returns the datetime value representing the current UTC time (Universal
> Time Coordinate or Greenwich Mean Time). The current UTC time is
> derived from the current local time and the time zone setting in the
> operating system of the computer on which SQL Server is running.
Well, if you're comparing two datetime values:
2005-12-31
2006-06-01
If you're in a timezone that observes daylight savings time, your
calculation is going to be an hour off (which way depends on what is
currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
an hour off in the other direction the next time the daylight savings time
goes on or off.
The calendar table can help solve this problem by giving you the offset on
each of the dates in question, allowing you to adjust each date accordingly.|||You also have to take into account that different areas change their clocks
on different dates, so you may need to create a second table with each time
zone and the date/time that they change their clocks.
That, and some areas (Arizona for example) do not use daylight savings time
at all.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OES60M7YGHA.4144@.TK2MSFTNGP04.phx.gbl...
> Well, if you're comparing two datetime values:
> 2005-12-31
> 2006-06-01
> If you're in a timezone that observes daylight savings time, your
> calculation is going to be an hour off (which way depends on what is
> currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
> an hour off in the other direction the next time the daylight savings time
> goes on or off.
> The calendar table can help solve this problem by giving you the offset on
> each of the dates in question, allowing you to adjust each date
accordingly.
>|||> You also have to take into account that different areas change their
> clocks
> on different dates, so you may need to create a second table with each
> time
> zone and the date/time that they change their clocks.
Or an extra column for each timezone (reproduce the tinyints instead of the
wider date values).

> That, and some areas (Arizona for example) do not use daylight savings
> time
> at all.
Right, Indiana just changed. Next year, the formula for determining the
dates changed in the US, so I think a lot of people who hav used an inline
calculation for this are either already working on fixing it or have plenty
of work to do over the winter. Since we used a calendar table in all of our
implementations, we don't have to worry about it... a simple update
statement corrects all future data until they waffle again.|||a column for each timezone seems much more complex than a single table with
one row each.
However, the benefit to doing it with columns is that you don't run into
problems when the timezone rules change. In the case of Indiana, you would
update the Indiana column in the calendar table for those date ranges. With
a separate table you would need to store the date that the rules changed and
always make sure you are joining to the correct row. I think I like your
idea of multiple columns better.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O%23NxvE8YGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Or an extra column for each timezone (reproduce the tinyints instead of
the
> wider date values).
>
> Right, Indiana just changed. Next year, the formula for determining the
> dates changed in the US, so I think a lot of people who hav used an inline
> calculation for this are either already working on fixing it or have
plenty
> of work to do over the winter. Since we used a calendar table in all of
our
> implementations, we don't have to worry about it... a simple update
> statement corrects all future data until they waffle again.
>