Advanced Search

Find off days in a date range using mysql

/ Published in: MySQL
Save to your folder(s)
A function to get off days inside mySQL

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

You need to login to post a comment.