0

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

Omer
  • 11
  • 3
  • Does this answer your question? [mysql-function to count days between 2 dates excluding weekends](https://stackoverflow.com/questions/18302181/mysql-function-to-count-days-between-2-dates-excluding-weekends) – Meher Ullah Khan Raj Jan 26 '22 at 09:37

3 Answers3

0

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

Moulitharan M
  • 729
  • 4
  • 15
0
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
toffler
  • 1,231
  • 10
  • 27
  • Ive tried this before but when using the dates like this SELECT ((DATEDIFF('2022-01-02', '2021-12-30')) - ((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 ....... it gives 105 days which is not right – Omer Jan 26 '22 at 09:52
  • in this case it wont work for the dates in the same year e.g. SELECT ((DATEDIFF('2022-01-04', '2022-01-02'))+1 - ((WEEK('2022-01-04') - WEEK('2022-01-02') + 52) * 2) - (case when weekday('2022-01-04') = 5 then 1 else 0 end) - (case when weekday('2022-01-02') = 4 then 1 else 0 end)) as DifD gives wrong days count (-101) – Omer Jan 26 '22 at 10:48
0

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;

Omer
  • 11
  • 3