Skip to content

Instantly share code, notes, and snippets.

@kazeno
Last active August 10, 2019 21:18
Show Gist options
  • Save kazeno/8bad9453d1e4d2aed33e6af14d1aa7a1 to your computer and use it in GitHub Desktop.
Save kazeno/8bad9453d1e4d2aed33e6af14d1aa7a1 to your computer and use it in GitHub Desktop.
MySQL Function for Calculating the Working Days between 2 Dates: https://blag.kazeno.net/development/mysql-function-for-calculating-the-working-days-between-2-dates
DELIMITER $$
CREATE FUNCTION WORKDAYSDATEDIFF (date1 DATE, date2 DATE, workdays VARCHAR(7))
RETURNS INT DETERMINISTIC
/* Calculates working days between 2 dates, work days are supplied as a string with numbers from 0-6 corresponding to WEEKDAY, for example Mon-Fri would be '01234' */
BEGIN
DECLARE date_start DATE;
DECLARE date_end DATE;
DECLARE total_days INT;
DECLARE weeks INT;
DECLARE start_weekday INT;
DECLARE end_weekday INT;
DECLARE count INT;
DECLARE iter INT;
IF (date1 IS NULL OR date2 IS NULL OR DATE('0000-00-00') IN(date1, date2)) THEN RETURN -1; END IF;
SET date_start = LEAST(date1, date2);
SET date_end = GREATEST(date1, date2);
SET total_days = DATEDIFF(date_end, date_start);
SET weeks = FLOOR(total_days/7);
SET start_weekday = WEEKDAY(date_start);
SET end_weekday = WEEKDAY(date_end);
SET count = CHAR_LENGTH(workdays)*weeks;
IF (MOD(total_days, 7) = 0) THEN RETURN count; END IF;
SET iter = start_weekday;
LOOP
IF (iter = end_weekday) THEN RETURN count; END IF;
IF (INSTR(workdays, CONCAT(iter)) > 0) THEN SET count = count +1; END IF;
IF (iter > 5) THEN SET iter = 0; ELSE SET iter = iter +1; END IF;
END LOOP;
END$$
DELIMITER ;
SELECT WORKDAYSDATEDIFF ('2019-07-05', '2019-08-05', '01234')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment