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 1. Count 2. If beginning and ending weekdays are the same, then if they're Tuesday, the answer is 3. Otherwise, if the beginning weekday <= the ending weekday, then if Tuesday is between them, the answer is 4. Otherwise the ending weekday is less than the starting weekday; if Tuesday >= the starting weekday or <= the ending weekdayy, the answer is For a convenient data source, we'll use the two date columns 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) |