Mysql function to find the number of working days between two dates

Constructing a MySQL function

Elevating your game? Ditch the simple solution and opt for a custom function. Similar to Excel but without needing the mouse is NETWORKDAYS(), a function to count workdays accounting for those sweet, sweet holidays.

Upgrading your function

Pitch your tent with MySQL, create a custom function, BusinessDaysBetweenDates:

DELIMITER //
CREATE FUNCTION BusinessDaysBetweenDates(start_date DATE, end_date DATE)
RETURNS INT
BEGIN
  DECLARE total_days INT DEFAULT 0;
  DECLARE iter_date DATE;

  IF(start_date > end_date) THEN -- Some like to live in the past, but not us. 
    RETURN total_days;
  END IF;

  SET iter_date = start_date;
  WHILE iter_date <= end_date DO
    IF DAYOFWEEK(iter_date) BETWEEN 2 AND 6 THEN
      SET total_days = total_days + 1;
    END IF;
    SET iter_date = iter_date + INTERVAL 1 DAY; -- Sunrises are overrated, let's skip 'em.
  END WHILE;

  RETURN total_days;
END //
DELIMITER ;


Hail it like a SQL Caesar:

SELECT BusinessDaysBetweenDates('2023-01-01', '2023-01-10') as working_days;


Crafting a calendar table

Going holiday mad? Build a calendar table and list down when you can put your out-of-office reply on:

CREATE TABLE Calendar (
  date DATE PRIMARY KEY,
  is_weekend BIT, -- The glorious two days in a cycle.
  is_holiday BIT  -- Fun is mandatory, attendance is not.
);

-- Populate your Calendar


Tweak the function to peep into the Calendar table when marking days.

Considering holidays

Hosting a holiday spirit, or just want some days off? Choreograph a subquery dance in your function:

WHILE iter_date <= end_date DO
  IF (SELECT is_weekend FROM Calendar WHERE date = iter_date) = 0 
    AND (SELECT is_holiday FROM Calendar WHERE date = iter_date) = 0 THEN
      SET total_days = total_days + 1; -- Yaay! Found one!
  END IF;
  SET iter_date = iter_date + INTERVAL 1 DAY; -- Couldn't find perfect date, let's swiping to next.
END WHILE;


Optimizing the robustness and accuracy

Aiming for the MVP of SQL scripts? Wrestle with edge cases to ensure your function doesn't trip up where start and end dates want a weekend getaway.

Battle of boundaries

Grit your teeth, it's validation time. If your dates bookend on weekends, let's fix them to weekdays.

IF DAYOFWEEK(start_date) == 7 THEN -- Sunday, caught ya!
  SET start_date = start_date + INTERVAL 2 DAY;
ELSEIF DAYOFWEEK(start_date) == 1 THEN -- Saturday, nope!
  SET start_date = start_date + INTERVAL 1 DAY;
END IF;


Act as if it's always Friday on the end date.

Squeezing performance

Overwhelmed by huge date ranges? Chop it down. Temporary tables to stockpile intermediate results or nut and bolts of date evaluation.

Script testing

Earn bragging rights by check and balancing your functions. Hard day’s work calls for a testing nightcap.

SELECT BusinessDaysBetweenDates('2023-01-01', '2023-01-10') as working_days; -- Expected elixir: 6


Battle test your functions against real-world time frames, because you, my friend, are building heroes of functions!