By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION FN_CNT_Working_days(StartDate DATE,
EndDate DATE)
RETURNS INT
BEGIN
DECLARE WORKING_DAYS INT;
SELECT
(DATEDIFF(EndDate, DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 16))) + 1)
-(FLOOR(DATEDIFF(EndDate, DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 16)))/7) * 2)
-(CASE WHEN DAYNAME(DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 16))) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DAYNAME(EndDate) = 'Saturday' THEN 1 ELSE 0 END)
+
(DATEDIFF(DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 15)), StartDate) + 1)
-(FLOOR(DATEDIFF(DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 15)), StartDate)/7))
-(CASE WHEN DAYNAME(StartDate) = 'Sunday' THEN 1 ELSE 0 END)
INTO WORKING_DAYS;
RETURN (WORKING_DAYS);
END
SELECT FN_CNT_Working_days('2019-10-01', '2019-10-31');
| FN_CNT_Working_days('2019-10-01', '2019-10-31') |
|---|
| 25 |