MySQL - Get bussiness/Off days

MySQL Version >5.0

Ever wondered how would you get a list of business dates/Off dates from a specific date range in MySQL? here is a simple way to do so...

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`uGetBussinessDays`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `uGetBussinessDays`(in_sDate DATE, in_eDate DATE)
READS SQL DATA
BEGIN
DECLARE l_sDate DATE;
SET l_sDate = in_sDate;
DROP TABLE IF EXISTS _tblBussinessDays;
DROP TABLE IF EXISTS _tblHolidaysDays;
CREATE TEMPORARY TABLE _tblBussinessDays(BussinessDays date);
CREATE TEMPORARY TABLE _tblHolidaysDays(Offdays date);
IF ( in_sDate > in_eDate ) THEN
SELECT "Invalid dates supplied";
END IF;
WHILE l_sDate <= in_eDate DO IF (DAYNAME(l_sDate) = 'Sunday' ) THEN INSERT INTO _tblHolidaysDays VALUES(l_sDate); ELSEIF ( DAYNAME(l_sDate) = 'Saturday' ) THEN INSERT INTO _tblHolidaysDays VALUES(l_sDate); ELSE INSERT INTO _tblBussinessDays VALUES(l_sDate); END IF; SET l_sDate = DATE_ADD(l_sDate,INTERVAL 1 DAY); END WHILE; SELECT BussinessDays FROM _tblBussinessDays; SELECT Offdays FROM _tblHolidaysDays; END$$ DELIMITER ;


## Now call above procedure to get a list of Bussiness days/Off days

call uGetBussinessDays('2009-06-01','2009-06-11');

BussinessDays
==========
2009-06-01
2009-06-02
2009-06-03
2009-06-04
2009-06-05
2009-06-08
2009-06-09
2009-06-10
2009-06-11

Offdays
======
2009-06-06
2009-06-07