How to calculate number of days between two dates in Mysql excluding weekends (saturday and sunday)?

Question

How do I return the number of days between two dates minus the number of saturday and sundays on that period ? The DATEDIFF function on mysql gives me the number of days but doesnt exclude the weekends.

Example, I have two columns and want to add a third one that is the differente between the two.

I would appreciate any help.

Answer 1

Try using this

Declare @Date1 Datetime, @Date2 Datetime;

Select @Date1 = '8/1/2018', @Date2 = '8/7/2018'

Select Datediff(dd, @Date1 , @Date2) - (Datediff(wk, @Date1 , @Date2) * 2) - Case When Datepart(dw, @Date1) = 1 Then 1 Else 0 End + Case When Datepart(dw, @Date2) = 1 Then 1 Else 0 End

Answer 2

DROP FUNCTION IF EXISTS GetNumberOfDays;

CREATE FUNCTION GetNumberOfDays(P_Date1 varchar(20), P_Date2 varchar(20))
   RETURNS int(100)
BEGIN
   DECLARE x         INT;
   DECLARE V_Date1   date;
   DECLARE V_Date2   date;
   SET V_Date1 := STR_TO_DATE(P_Date1, '%d/%m/%Y');
   SET V_Date2 := STR_TO_DATE(P_Date2, '%d/%m/%Y');
   SET x := 0;

   WHILE V_Date1 <= V_Date2
   DO
      IF (DAYOFWEEK(V_Date1) != 7 AND DAYOFWEEK(V_Date1) != 1)
      THEN
      BEGIN
          SET x = x + 1;
      END;
      END IF;

      SET V_Date1 := DATE_ADD(V_Date1, INTERVAL 1 DAY);
   END WHILE;

  RETURN x;
END;



SELECT GetNumberOfDays('01/08/2018', '11/08/2018')