|Date arithmetic is deceptively hard. One way to appreciate the difficulties is to read Chapter 21 in our book. Another is to try to calculate the number of Tuesdays (or another weekday) between two dates. It's not a back-of-the-napkin problem.
An earlier formula we had for this problem sometimes gave incorrect results. As a debugging aid, we wrote a brute force calculator for the problem:
SET GLOBAL log_bin_trust_function_creators=1; DROP FUNCTION IF EXISTS DayCount; DELIMITER | CREATE FUNCTION DayCount( d1 DATE, d2 DATE, daynum SMALLINT ) RETURNS INT BEGIN DECLARE days INT DEFAULT 0; IF D1 IS NOT NULL AND D2 IS NOT NULL THEN WHILE D1 <= d2 DO BEGIN IF DAYOFWEEK(d1) = daynum THEN SET days=days+1; END IF; SET d1 = ADDDATE(d1, INTERVAL 1 DAY); END; END WHILE; END IF; RETURN days; END; | DELIMITER ; select daycount('2008-3-16','2008-3-28',7) AS 'Sats from 2008-3-16 through 2008-3-28';You would not want to use that function on long date spans in a big table, but it will do for testing.
Now, how to count the number of Tuesdays, say, between two dates? The basic logic is:
1. Count weeks between the two dates.
2. If beginning and ending weekdays are the same, then if they're Tuesday, the answer is weeks+1, otherwise it's just weeks.
3. Otherwise, if the beginning weekday <= the ending weekday, then if Tuesday is between them, the answer is weeks+1, otherwise it's just weeks.
4. Otherwise the ending weekday is less than the starting weekday; if Tuesday >= the starting weekday or <= the ending weekdayy, the answer is weeks+1, otherwise it's just weeks.
For a convenient data source, we'll use the two date columns orderdate and shippeddate in the orders table of the NorthWind database, and we'll use our brute force function DayCount() to check results:
SET @day = 3; SELECT DATE_FORMAT(orderdate,'%y%m%d') AS OrdDt, DATE_FORMAT(shippeddate,'%y%m%d') AS ShipDt, LEFT(DAYNAME(orderdate),3) AS D1, LEFT(DAYNAME(shippeddate),3) AS D2, @dow1 := DAYOFWEEK(orderdate) AS 'dw1', @dow2 := DAYOFWEEK(shippeddate) AS 'dw2', @days := DATEDIFF(shippeddate,orderdate) AS Days, @wks := FLOOR( @days / 7 ) AS Wks, FLOOR( IF( @dow1 = @dow2, IF( @day = @dow1, @wks+1, @wks), IF( @dow1 < @dow2, IF( @day BETWEEN @dow1 AND @dow2, @wks+1, @wks), IF( @day >= @dow1 OR @day <= @dow2, @wks+1, @wks ) ) ) ) AS Res, DayCount(DATE(orderdate),DATE(shippeddate),@day) AS Chk FROM orders HAVING !ISNULL(res-chk) AND res-chk <> 0; Empty set (0.00 sec)No errors. We get the same result for @day = 1, 2, 4, 5, 6 and 7.
But the formula is buried in the specifics of one table, so abstract it to a reusable function:
DROP FUNCTION IF EXISTS NamedDaysBetween; DELIMITER | CREATE FUNCTION NamedDaysBetween( d1 DATE, d2 DATE, daynum SMALLINT ) RETURNS INT BEGIN DECLARE dow1, dow2, wks, days INT; IF !ISNULL(d1) AND !ISNULL(d2) THEN SET dow1 = DAYOFWEEK( d1 ); SET dow2 = DAYOFWEEK( d2 ); SET days = DATEDIFF( d2, d1 ); SET wks = FLOOR( days / 7 ); SET days = IF( dow1 = dow2, IF( daynum = dow1, wks+1, wks), IF( dow1 < dow2, IF( daynum BETWEEN dow1 AND dow2, wks+1, wks), IF( daynum >= dow1 OR daynum <= dow2, wks+1, wks ) ) ); END IF; RETURN days; END; | DELIMITER ;Again check it against lots of date value pairs:
SELECT nameddaysbetween(orderdate,shippeddate,3) - daynamecount(orderdate,shippeddate,3) AS diff FROM orders HAVING !ISNULL(diff) AND diff <> 0; Empty set (0.00 sec)