MySQL days/weekdays between two dates

[RudyM]

Hi all,

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 ;

[cereal]

To get the difference between two dates you can use datediff():

• http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff

To get the difference without weekends you could also do:

drop function if exists diffdate;
delimiter //
CREATE FUNCTION diffdate(date2 DATETIME, date1 DATETIME)
RETURNS INTEGER DETERMINISTIC
BEGIN
DECLARE dt1 DATETIME;
DECLARE i INT;
DECLARE wd INT UNSIGNED;
SET i = 0;
SET wd = DAYOFWEEK(date1);
SET dt1 = date1;

IF DATEDIFF(date2, dt1) > 0 THEN
    WHILE DATEDIFF(date2, dt1) > 0 DO
        SET dt1:=DATE_ADD(dt1, INTERVAL 1 DAY);
        SET wd:=DAYOFWEEK(dt1);
        CASE WHEN wd in(2,3,4,5,6) THEN SET i:=i+1;
        ELSE SET i:=i;
        END CASE;
    END WHILE;
ELSEIF DATEDIFF(date2, dt1) < 0 THEN
    WHILE DATEDIFF(date2, dt1) < 0 DO
        SET dt1:=DATE_SUB(dt1, INTERVAL 1 DAY);
        SET wd:=DAYOFWEEK(dt1);
        CASE WHEN wd in(2,3,4,5,6) THEN SET i:=i-1;
        ELSE SET i:=i;
        END CASE;
    END WHILE;
END IF;

RETURN i;
END//
delimiter ;

then you can play your queries like these:

> select diffdate(now(), '2016-05-31 00:00:00') as 'without weekends', datediff(now(), '2016-05-31 00:00:00') as 'with weekends';
+------------------+---------------+
| without weekends | with weekends |
+------------------+---------------+
|               -2 |            -4 |
+------------------+---------------+
1 row in set (0.00 sec)

> select diffdate('2016-05-31 00:00:00', now()) as 'without weekends', datediff('2016-05-31 00:00:00', now()) as 'with weekends';
+------------------+---------------+
| without weekends | with weekends |
+------------------+---------------+
|                2 |             4 |
+------------------+---------------+
1 row in set (0.00 sec)

> select diffdate(now(), now()) as 'without weekends', datediff(now(), now()) as 'with weekends';
+------------------+---------------+
| without weekends | with weekends |
+------------------+---------------+
|                0 |             0 |
+------------------+---------------+
1 row in set (0.00 sec)

The above is based on this function:

• https://www.daniweb.com/programming/web-development/threads/473926/display-and-insert-expected-date-of-release-on-submit-php-mysql#post2069341

For the variables look at:

• http://dev.mysql.com/doc/refman/5.7/en/user-variables.html

If you still have doubts let us know, bye!

[Harold_9]

Works like a charm! Thank you very much

[kccole01]

I suggest that you can reduce the amount of code by factoring the problem like this:

1. Get the total number of days between start and end date.
2. Get the number of weeks between start and end date, and double it to get the number of weekend days in the period.
3. Subtract the results of 2 from the results of 1.

I don't have mysql but I've used this algorithm in many SPROCs in SQL Server over many years.

Good luck.

--K