DELIMITER $$ DROP PROCEDURE IF EXISTS zj$$ CREATE PROCEDURE insertNoday(i DATE,j DATE) BEGIN DECLARE num VARCHA(4); WHILE i<=j DO SELECT WEEKDAY(i) INTO num ; IF num<5 THEN INSERT INTO sys_invalid_day(invalid_day) VALUES(i); END IF; SET i=i+INTERVAL 1 DAY; END WHILE; END$$ DELIMITER ;CALL insertNoday('2015-01-01','2015-05-31')
需要一个日历表,然后用 select 语句来实现。
SET @days = DATEDIFF(@dTo, @dFrom)+1;
SELECT @days - FLOOR(@days/7)*2
DROP PROCEDURE IF EXISTS zj$$
CREATE PROCEDURE insertNoday(i DATE,j DATE)
BEGIN
DECLARE num VARCHA(4);
WHILE i<=j DO
SELECT WEEKDAY(i) INTO num ;
IF num<5 THEN
INSERT INTO sys_invalid_day(invalid_day) VALUES(i);
END IF;
SET i=i+INTERVAL 1 DAY;
END WHILE;
END$$
DELIMITER ;CALL insertNoday('2015-01-01','2015-05-31')