how to calculate mysql date diff between two dates excluding Fridays and Saturdays?-CodePudding

how to calculate mysql date diff between two dates excluding Fridays and Saturdays as Fridays and Saturdays are off days

i've tried

SELECT ((DATEDIFF('2022-01-02', '2021-12-30')) 1 - ((WEEK('2022-01-02') - WEEK('2021-12-30')) * 2) - (case when weekday('2022-01-02') = 5 then 1 else 0 end) - (case when weekday('2021-12-30') = 4 then 1 else 0 end)) as DifD but it gives wrong count (105 days) and suppose to give 2 days

CodePudding user response：

you need to use case expression to check if the day is Thursday then add -2

CodePudding user response：

SELECT ((DATEDIFF('2014-10-25', '2014-10-15')) -
        ((WEEK('2014-10-25') - WEEK('2014-10-15')) * 2) -
        (case when weekday('2014-10-25') = 5 then 1 else 0 end) -
        (case when weekday('2014-10-15') = 4 then 1 else 0 end)) as DifD

// Total difference

SELECT ((DATEDIFF('2014-10-25', '2014-10-15'))

// calendar week(s) ---> not including the year

WEEK('2022-01-02') // = 1
WEEK('2021-12-30') // = 52

// get weekday, where 0 is monday and 6 is sunday

case when weekday('2022-01-02') = 4 // (or 5) subtract 1

If you want this to work on different years, like your example above u have to add 52 weeks for every year difference. a working example would be:

SELECT ((DATEDIFF('2022-01-02', '2021-12-30')) 1 - ((WEEK('2022-01-02') - WEEK('2021-12-30')   52) * 2) - (case when weekday('2022-01-02') = 5 then 1 else 0 end) - (case when weekday('2021-12-30') = 4 then 1 else 0 end)) as DifD

CodePudding user response：

Thank you everyone for helping, i found the answer from here mysql-function to count days between 2 dates excluding weekends

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE) RETURNS INT 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)) = 6) - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);

sql query for any dates e.g. : SELECT TOTAL_WEEKDAYS('2022-01-09', '2022-01-12') weekdays1;