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.
>

DateDiff: Calculating working days

I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?

Yes, this is possible; however, normally you must account for holidays and when all is said and done I would suggest that you give a look to this article:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

This discusses establishing a calendar table which ought to help with computing "working days".

( Adolf: what does "NB" stand for? )

|||NB this article doesn't discuss different sets of holidays

e.g. england, wales, scotland, ireland

have different public holidays despite being in the UK

you may wish to add extra columns to cater for this|||thank you.. I've decided to pinch the calendar table off another db and will try to follow the examples.

DateDiff: Calculating working days

I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?

Yes, this is possible; however, normally you must account for holidays and when all is said and done I would suggest that you give a look to this article:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

This discusses establishing a calendar table which ought to help with computing "working days".

( Adolf: what does "NB" stand for? )

|||NB this article doesn't discuss different sets of holidays

e.g. england, wales, scotland, ireland

have different public holidays despite being in the UK

you may wish to add extra columns to cater for this|||thank you.. I've decided to pinch the calendar table off another db and will try to follow the examples.

DateDiff calculating ages incorrectly.

Hi,
I am trying to use datediff to calculate a persons age, based on their date
of brith. I am using the following function:
(datediff(year,[DOB],getdate()))
The formula calculates the ages correctly for people whose birthday falls on
a day and month before today (getdate()), but for those with a birthday afte
r
today it adds an extra year on.
Anyone got any suggestions about how to correctly calculate ages using a
date of birth?Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the same
as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
bothe sets of dates...
To calculate Age, use the following:
Year(@.D2) - Year(@.D1)
- Case When Month(@.D2) > Month(@.D1) Or
(Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
Else 0 End
You could put this in UDF...
Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
RETURNS TinyInt
As
Begin
Declare @.Age SmallInt
Set @.Age = Year(@.CurDT) - Year(@.DOB) -
Case When Month(@.CurDT) < Month(@.DOB) Then 1
When Month(@.CurDT) > Month(@.DOB) Then 0
When Day(@.CurDT) < Day(@.DOB) Then 1
Else 0 End
Return @.Age
End
"Enterprise Andy" wrote:

> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||http://groups.google.ca/group/micro...49c2e
c8
AMB
"Enterprise Andy" wrote:

> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||Hi Andy,
"Enterprise Andy" <EnterpriseAndy@.discussions.microsoft.com> wrote in
message news:10C0C6CD-965D-4608-A47E-E720ACE40DDE@.microsoft.com...
> Hi,
> I am trying to use datediff to calculate a persons age, based on their
> date
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
> on
> a day and month before today (getdate()), but for those with a birthday
> after
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?
Try this:
CREATE FUNCTION uf_YearsDifference (@.initialDate DATETIME,
@.finalDateDATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT CASE WHEN
DATEADD(YEAR, DATEDIFF(YEAR, @.initialDate, @.finalDate), @.initialDate) >
@.finalDate
THEN DATEDIFF(YEAR, @.initialDate, @.finalDate) - 1
ELSE DATEDIFF(YEAR, @.initialDate, @.finalDate)
END
)
END
Andrea - www.absistemi.it|||Many thanks. Saved me a lot of trouble!!!
"CBretana" wrote:
> Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
> exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the sa
me
> as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
> bothe sets of dates...
> To calculate Age, use the following:
> Year(@.D2) - Year(@.D1)
> - Case When Month(@.D2) > Month(@.D1) Or
> (Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
> Else 0 End
> You could put this in UDF...
> Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
> RETURNS TinyInt
> As
> Begin
> Declare @.Age SmallInt
> Set @.Age = Year(@.CurDT) - Year(@.DOB) -
> Case When Month(@.CurDT) < Month(@.DOB) Then 1
> When Month(@.CurDT) > Month(@.DOB) Then 0
> When Day(@.CurDT) < Day(@.DOB) Then 1
> Else 0 End
> Return @.Age
> End
>
> "Enterprise Andy" wrote:
>