DateDiff function does not return the correct number of weeks
Hint Ref: 021002050003
Hint Date: 05/02/2010
Hint Details:
PROBLEM: A customer reported that they were having trouble with the DateDiff function when using 'week' to find the number of weeks between two dates.
They reported that:
select datediff(week,'2010-01-01','2010-01-07') was 7 days and correctly returned 1 week.
However:
select datediff(week,'2010-01-03','2010-01-09') was clearly also 7 days, but returned 0 weeks.
SOLUTION: It should be noted that this is not an error.
When using 'week' it is important to remember that the function does NOT return the number of days divided by 7, but instead returns the number of Sundays between the two dates, including the second date but not the first.
To return the actual number of whole 7 days periods, use the following:
select (datediff(day,<date1>,<date2>)+1)/7
NOTE: For your information, the Sybase Reference Manual states the following:
This function calculates the number of date parts between two specified dates. The result is a signed integer value equal to (date2 - date1), in date parts.
DATEDIFF results are truncated, not rounded, when the result is not an even multiple of the date part.
When you use day as the date part, DATEDIFF returns the number of midnights between the two times specified, including the second date but not the first.
When you use month as the date part, DATEDIFF returns the number of first-of-the-months between two dates, including the second date but not the first.
When you use week as the date part, DATEDIFF returns the number of Sundays between the two dates, including the second date but not the first.
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)