Last active
August 10, 2019 21:18
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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