0

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 ;

Edited by RudyM: Procedure name change.

2
Contributors
1
Reply
15
Views
1 Year
Discussion Span
Last Post by cereal
0

To get the difference between two dates you can use 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:

For the variables look at:

If you still have doubts let us know, bye!

Edited by cereal

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.