Get Count of saturdays and sundays between two dates ?

 

Summary

 In this post, you can learn how to get count of number of Saturdays and sundays in given date range using Oracle and PLSQL.

 Sometimes you want to return the number of week days between two dates (i.e., the number of days excluding the weekend).

 The general strategy is to construct an expression that does the following:

• Take two dates that you want to get days in between and loop date range.
• In loop, get day from date.
• If day is Saturday (6) and Sunday (7) as per Julian Calendar then 1 and take weekend day count otherwise 0 and take weekday day count

 

Using PLSQL

DECLARE

    weekday_CNT        NUMBER := 0;
    weekend_CNT        NUMBER := 0;
    v_date             DATE;
    date_20160716      DATE;
    date_20160817      DATE;
    v_day_of_week      VARCHAR(1);
    v_dayFlg           CHAR(1);
    
BEGIN
  -- Date From 20160716  
  date_20160716 := TO_DATE('2016-07-16', 'YYYY-MM-DD');
  -- Date To 20160817  
  date_20160817 := TO_DATE('2016-08-17', 'YYYY-MM-DD');

  --（From 20160716 To 20160817）loop 
  FOR vTempdate IN TO_NUMBER(TO_CHAR(date_20160716, 'J')) ..
                   TO_NUMBER(TO_CHAR(date_20160817, 'J'))

  LOOP
     v_date := TO_DATE(vTempdate, 'J');
     -- Get day from date
     select TO_CHAR(TO_DATE(v_date, 'YYYY-MM-DD'), 'D') into v_day_of_week from dual;

   -- v_day_of_week is Saturday(6) or sunday(7) as per Julian calendar then dayflg is 1, otherwise 0
     IF v_day_of_week in ('6', '7' ) THEN
        v_dayFlg := '1';
        weekend_CNT := weekend_CNT + 1;
     ELSE
        v_dayFlg := '0';
        weekday_CNT := weekday_CNT + 1;
     END IF;
     
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('weekday_CNT: ' || weekday_CNT);
  DBMS_OUTPUT.PUT_LINE('weekend_CNT: ' || weekend_CNT);

  COMMIT;

END;

/

weekday_CNT: 23
weekend_CNT: 10

PL/SQL procedure successfully completed.

 Why Julian dates actually work is because Julian dates are basically a count of days since a fixed, known point in time you can use basic maths to determine whether or not it was a weekend or not, because you already know what day of the week day 1 was. Since other calendars have varying month lengths, year lengths, leap days and leap years, it is more difficult to derive information about the day of the week for other calendars, given a single date to work with.

Finding day of week given Julian day number

The US day of the week W1 can be determined from the Julian Day Number J with the expression:

W1 = mod(J + 1, 7)^[29]

W1	0	1	2	3	4	5	6
Day of the week	Sun	Mon	Tue	Wed	Thu	Fri	Sat

The ISO day of the week W0 can be determined from the Julian Day Number J with the expression:

W0 = mod (J, 7) + 1

W0	1	2	3	4	5	6	7
Day of the week	Mon	Tue	Wed	Thu	Fri	Sat	Sun

Reference found here.

Using Oracle

SELECT TO_DATE('2016-07-15', 'YYYY-MM-DD') AS START_DATE, 
       TO_DATE('2016-08-17', 'YYYY-MM-DD') AS END_DATE, 
       COUNT(*) saturdays_and_sundays 
FROM dual 
WHERE TO_CHAR(TO_DATE('2016-07-15', 'YYYY-MM-DD') + LEVEL, 'D') IN ('1','7')

CONNECT BY LEVEL <= TO_DATE('2016-08-17','YYYY-MM-DD') - TO_DATE('2016-07-15','YYYY-MM-DD')

Output

START_DATE	END_DATE	SATURDAYS_AND_SUNDAYS
2016/07/15	2016/08/17	10

Finally you created function to get count of Saturdays and Sundays in given date range.