I wanted to procedure to return number of days and weekdays between two dates. This is what I came up with and it seems to work ok, but I'd like to read what others think. And if there's already a single line function for this, oops. Also, I'm not too comfortable with when to use @variable or just variable. Thanks.
DELIMITER $$ CREATE PROCEDURE `DATEDIFF_WEEKDAYS`(IN `IN_from_date` DATE, IN `IN_to_date` DATE) BEGIN DECLARE TOTAL_WEEKDAYS INT DEFAULT 0; DECLARE TOTAL_DAYS INT DEFAULT 0; DECLARE FROM_DTE DATE DEFAULT NOW(); DECLARE TO_DTE DATE DEFAULT NOW(); DECLARE CURR_DTE DATE DEFAULT NOW(); IF(IN_from_date = IN_to_date) THEN SELECT 0; END IF; /* Check if FROM_DATE provided is later than */ IF(TO_DAYS(IN_from_date) > TO_DAYS(IN_to_date)) THEN SET FROM_DTE = IN_to_date; SET TO_DTE = IN_from_date; SET CURR_DTE = FROM_DTE; ELSE SET FROM_DTE = IN_from_date; SET TO_DTE = IN_to_date; SET CURR_DTE = FROM_DTE; END IF; SELECT FROM_DTE, TO_DTE, CURR_DTE; /* Loop through days between two dates, up to and including TO_DTE: */ WHILE(TO_DAYS(CURR_DTE) <= TO_DAYS(TO_DTE)) DO /* INCREMENT TOTAL_WEEKDAYS ONLY WHEN DAYOFWEEK IS 2 - 6. DAYS 1 and 7 are Sunday and Saturday (resp.) */ IF(DAYOFWEEK(CURR_DTE) <> 1 AND DAYOFWEEK(CURR_DTE) <> 7) THEN SET TOTAL_WEEKDAYS = TOTAL_WEEKDAYS + 1; END IF; SET TOTAL_DAYS = TOTAL_DAYS + 1; /* Update tracking date */ SET CURR_DTE = DATE_ADD(CURR_DTE, INTERVAL 1 DAY); END WHILE; SELECT TOTAL_DAYS, TOTAL_WEEKDAYS; END$$ DELIMITER ;