Mysql get end date excluding weekends

Question

in Mysql I am trying to get end date excluding weekends

like my function GET_DATE_WEEKDAYS('2022-03-24',5) giving - 2022-03-17 which is fine

it is giving issue when i increase date range , or when end date is on weekend or , feb month

like GET_DATE_WEEKDAYS('2022-03-24',10) giving - 2022-03-11 , it should give 2022-03-10

this is my function body

BEGIN

    DECLARE totalweekdays INT;
    DECLARE newinputcount INT;
    SET newinputcount = inputcount;
    SET totalweekdays = TOTAL_WEEKDAYS(inputdate,DATE_ADD(inputdate, INTERVAL -newinputcount DAY));
    label1: WHILE totalweekdays <= inputcount       
    DO  
        SET totalweekdays = totalweekdays + 1;
        SET newinputcount = newinputcount + 1;
    END WHILE label1;   

    IF DAYNAME(DATE_ADD(inputdate, INTERVAL -newinputcount DAY)) = 'Sunday' THEN
      SET newinputcount = newinputcount + 2;

   ELSEIF DAYNAME(DATE_ADD(inputdate, INTERVAL -newinputcount DAY)) = 'Saturday' THEN
      SET newinputcount = newinputcount + 1;

   ELSE
      SET newinputcount = newinputcount;

   END IF;
   RETURN DATE_ADD(inputdate, INTERVAL -newinputcount DAY);

END

fn TOTAL_WEEKDAYS is

RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7)

can there be any generic solution for it

Answer 1

Here is the function and a test query.

create function GET_DATE_WEEKDAYS(endDate date, numberWeekDays int)
RETURNS date DETERMINISTIC
BEGIN
    DECLARE startDate date;
    DECLARE weeks int;
    DECLARE days int;
    SET weeks = numberWeekDays / 7;
    SET days = numberWeekDays - (5 * weeks);
    SET startDate = date_add(endDate, interval -1 * weeks week);
    while (days > 0) do
      set startDate = date_add(startDate, interval -1 day);
      set days = days - case when weekday(startDate) < 5 then 1 else 0 end ;
    end while;
    return startDate;
END;

create table d (id int); insert into d values (1),(1),(1),(1),(1),(1);

set @days = 1;
SELECT 
  '2022-03-24' as endDate, 
  @days := @days + 1 as weekdays, 
  GET_DATE_WEEKDAYS('2022-03-24', @days ) as startDate
from d a, d b;

endDate    | weekdays | startDate 
:--------- | -------: | :---------
2022-03-24 |        2 | 2022-03-22
2022-03-24 |        3 | 2022-03-21
2022-03-24 |        4 | 2022-03-17
2022-03-24 |        5 | 2022-03-17
2022-03-24 |        6 | 2022-03-16
2022-03-24 |        7 | 2022-03-15
2022-03-24 |        8 | 2022-03-14
2022-03-24 |        9 | 2022-03-11
2022-03-24 |       10 | 2022-03-10
2022-03-24 |       11 | 2022-03-09
2022-03-24 |       12 | 2022-03-08
2022-03-24 |       13 | 2022-03-07
2022-03-24 |       14 | 2022-03-04
2022-03-24 |       15 | 2022-03-03
2022-03-24 |       16 | 2022-03-02
2022-03-24 |       17 | 2022-03-01
2022-03-24 |       18 | 2022-02-28
2022-03-24 |       19 | 2022-02-25
2022-03-24 |       20 | 2022-02-24
2022-03-24 |       21 | 2022-02-23
2022-03-24 |       22 | 2022-02-22
2022-03-24 |       23 | 2022-02-21
2022-03-24 |       24 | 2022-02-18
2022-03-24 |       25 | 2022-02-17
2022-03-24 |       26 | 2022-02-16
2022-03-24 |       27 | 2022-02-15
2022-03-24 |       28 | 2022-02-14
2022-03-24 |       29 | 2022-02-11
2022-03-24 |       30 | 2022-02-10
2022-03-24 |       31 | 2022-02-09
2022-03-24 |       32 | 2022-02-08
2022-03-24 |       33 | 2022-02-07
2022-03-24 |       34 | 2022-02-04
2022-03-24 |       35 | 2022-02-03
2022-03-24 |       36 | 2022-02-02
2022-03-24 |       37 | 2022-02-01

db<>fiddle here