Count Tuesdays between two dates

from the Artful Common Queries page


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 datasource, 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)


Return to the Artful Common Queries page