Mysql获取不包括周末的结束日期

Question

在Mysql中，我试图获得不包括周末的结束日期

就像我的函数GET_DATE_WEEKDAYS('2022-03-24'，5(给出-2022-03-17，这是一个很好的

当我增加日期范围时，或者当结束日期在周末或二月时，它会发出问题

与GET_DATE_WEEKDAYS("2022-03-24"，10(给出的-2022-03-11一样，它应该给出2022-03-10

这是我的功能体

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为

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)

有什么通用的解决方案吗

Answer 1

这是函数和测试查询。

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<gt；小提琴这里