Posted By

aamirrajpoot on 07/14/11


Tagged

offDays


Versions (?)

Find off days in a date range using mysql


 / Published in: MySQL
 

A function to get off days inside mySQL

  1. DELIMITER $$
  2.  
  3. DROP FUNCTION IF EXISTS `numWeekends`$$
  4.  
  5. CREATE FUNCTION `numWeekends`(first_date DATE, second_date DATE, str_weekends VARCHAR(50)) RETURNS INT(11)
  6. DETERMINISTIC
  7. BEGIN
  8. DECLARE start_date DATE;
  9. DECLARE end_date DATE;
  10. DECLARE diff INT DEFAULT 0;
  11. DECLARE dayofweekint INT;
  12. IF (first_date < second_date) THEN
  13. SET start_date = first_date;
  14. SET end_date = second_date;
  15. ELSE
  16. SET start_date = second_date;
  17. SET end_date = first_date;
  18. END IF;
  19.  
  20. WHILE start_date <= end_date DO
  21. SET dayofweekint = DAYOFWEEK(start_date) - 1;
  22. IF
  23. (dayofweekint < 1)
  24. THEN
  25. SET dayofweekint = 7;
  26. END IF;
  27. IF
  28. (CONCAT('[[:<:]]', str_weekends, '[[:<:]]') REGEXP dayofweekint)
  29. THEN
  30. SET diff = diff + 1;
  31. END IF;
  32. SET start_date = DATE_ADD(start_date,INTERVAL 1 DAY);
  33. END WHILE;
  34.  
  35. RETURN diff;
  36.  
  37. END$$
  38.  
  39. DELIMITER ;

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: aamirrajpoot on July 14, 2011

usage

SELECT numWeekends('2011-07-01', '2011-07-31', '6'); SELECT numWeekends('2011-07-01', '2011-07-31', ''); SELECT numWeekends('2011-07-01', '2011-07-31', '4,5');

You need to login to post a comment.