SELECT Count(CASE
WHEN To_char(date_col, 'D') IN ( '6', '7' ) THEN 1
ELSE NULL
END)
FROM (SELECT To_date('&start_date', 'dd/mm/yyyy')
+ ROWNUM - 1 date_col
FROM dual
CONNECT BY LEVEL <= To_date('&end_date', 'dd/mm/yyyy') -
To_date('&start_date', 'dd/mm/yyyy') + 1);
It is slightly more efficient and can handle any date range. Â The where clause is removed as we can handle the limit with the connect by clause.
CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
RETURN NUMBER
DETERMINISTIC
IS
v_date DATE := p_start_date;
v_count INTEGER := 0;
BEGIN
WHILE v_date <= p_end_date
LOOP
IF TO_CHAR(v_date, 'Dy') IN ('Sat', 'Sun')
THEN
v_count := v_count + 1;
END IF;
v_date := v_date + 1;
END LOOP;
RETURN v_count;
END;
CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
RETURN NUMBER
DETERMINISTIC
IS
v_date DATE := p_start_date;
v_count INTEGER := 0;
-- Derive the Weekend abbreviations regardless of NLS settings by using known dates
v_saturday_abbrv VARCHAR2(5) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
v_sunday_abbrv VARCHAR2(5) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday
BEGIN
WHILE v_date <= p_end_date
LOOP
IF TO_CHAR(v_date, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
THEN
v_count := v_count + 1;
END IF;
v_date := v_date + 1;
END LOOP;
RETURN v_count;
END;
/
SQL> select value from v$nls_parameters where parameter = 'NLS_LANGUAGE';
VALUE
----------------------------------------------------------------
AMERICAN
SQL> select TO_CHAR(DATE '2015-06-06', 'Dy'), TO_CHAR(DATE '2015-06-07', 'Dy') from dual;
TO_ TO_
--- ---
Sat Sun
SQL> select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual;
COUNT_WEEKEND_DAYS(DATE'2015-06-01',DATE'2015-06-10')
-----------------------------------------------------
2
SQL> alter session set nls_language ='DUTCH';
Session altered.
SQL> select value from v$nls_parameters where parameter = 'NLS_LANGUAGE';
VALUE
----------------------------------------------------------------
DUTCH
SQL> select TO_CHAR(DATE '2015-06-06', 'Dy'), TO_CHAR(DATE '2015-06-07', 'Dy') from dual;
TO TO
-- --
Za Zo
SQL> select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual;
COUNT_WEEKEND_DAYS(DATE'2015-06-01',DATE'2015-06-10')
-----------------------------------------------------
2
CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
RETURN NUMBER
DETERMINISTIC
IS
v_date DATE := p_start_date;
v_count INTEGER := 0;
-- Derive the Weekend abbreviations regardless of NLS settings by using known dates
v_saturday_abbrv VARCHAR2(10) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
v_sunday_abbrv VARCHAR2(10) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday
BEGIN
WHILE v_date <= p_end_date
LOOP
IF TO_CHAR(v_date, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
THEN
v_count := v_count + 1;
END IF;
v_date := v_date + 1;
END LOOP;
RETURN v_count;
END;
/
CREATE OR replace FUNCTION Count_weekend_days(p_start_date IN DATE,
p_end_date IN DATE)
RETURN NUMBER deterministic
IS
v_count INTEGER := 0;
-- Derive the Weekend abbreviations regardless of NLS settings by using known dates
v_saturday_abbrv VARCHAR2(10) := To_char(DATE'2015-06-06', 'Dy');
-- a known Saturday
v_sunday_abbrv VARCHAR2(10) := To_char(DATE '2015-06-07', 'Dy');
-- a known Sunday
BEGIN
SELECT Count(1)
INTO v_count
FROM (SELECT To_char(p_start_date + ROWNUM - 1, 'Dy') dname
FROM dual
CONNECT BY LEVEL <= p_end_date - p_start_date + 1)
WHERE dname IN ( v_saturday_abbrv, v_sunday_abbrv );
RETURN v_count;
END;
/
Not that either way is right or wrong. Â Just different. Â If this is a function that would get called a lot, you may see a performance difference without the looping.
DECLARE
c_loop_count CONSTANT INTEGER := 10;
v_time1 TIMESTAMP;
v_time2 TIMESTAMP;
v_time3 TIMESTAMP;
v_diff1 NUMBER;
v_diff2 NUMBER;
v_dummy NUMBER;
BEGIN
v_time1 := SYSTIMESTAMP;
FOR n IN 1 .. c_loop_count
LOOP
FOR x IN 1 .. 100
LOOP
FOR y IN x + 1 .. x + 100
LOOP
v_dummy := count_weekend_days(SYSDATE + x, SYSDATE + y);
END LOOP;
END LOOP;
END LOOP;
v_time2 := SYSTIMESTAMP;
FOR n IN 1 .. c_loop_count
LOOP
FOR x IN 1 .. 100
LOOP
FOR y IN x + 1 .. x + 100
LOOP
v_dummy := count_weekend_dayssql(SYSDATE + x, SYSDATE + y);
END LOOP;
END LOOP;
END LOOP;
v_time3 := SYSTIMESTAMP;
v_diff1 :=
EXTRACT(SECOND FROM (v_time2 - v_time1))
+ EXTRACT(MINUTE FROM (v_time2 - v_time1)) * 60
+ EXTRACT(HOUR FROM (v_time2 - v_time1)) * 3600
+ EXTRACT(DAY FROM (v_time2 - v_time1)) * 86400;
v_diff2 :=
EXTRACT(SECOND FROM (v_time3 - v_time2))
+ EXTRACT(MINUTE FROM (v_time3 - v_time2)) * 60
+ EXTRACT(HOUR FROM (v_time3 - v_time2)) * 3600
+ EXTRACT(DAY FROM (v_time3 - v_time2)) * 86400;
DBMS_OUTPUT.put_line(RPAD('-', 80, '-'));
DBMS_OUTPUT.put_line('Test1: ' || v_time1 || ' ' || v_time2 || ' ' || v_diff1);
DBMS_OUTPUT.put_line('Test2: ' || v_time2 || ' ' || v_time3 || ' ' || v_diff2);
DBMS_OUTPUT.put_line('Test1 took: ' || v_diff1 / v_diff2 || ' times as long as Test2');
DBMS_OUTPUT.put_line(RPAD('-', 80, '-'));
END;
--------------------------------------------------------------------------------
Test1: 10-JUN-15 10.46.43.917757 AM 10-JUN-15 10.46.47.057447 AM 3.13969
Test2: 10-JUN-15 10.46.47.057447 AM 10-JUN-15 10.46.55.494899 AM 8.437452
Test1 took: .3721135243199013161793394498718333449482 times as long as Test2
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
RETURN NUMBER
DETERMINISTIC
IS
v_start DATE := TRUNC(p_start_date);
v_end DATE := TRUNC(p_end_date);
v_date DATE := v_start;
-- Derive the Weekend abbreviations regardless of NLS settings by using known dates
v_saturday_abbrv VARCHAR2(10) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
v_sunday_abbrv VARCHAR2(10) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday
v_start_sunday DATE;
v_end_sunday DATE := NEXT_DAY(v_end - 7, v_sunday_abbrv);
v_count INTEGER := 0;
BEGIN
IF v_start = v_end AND TO_CHAR(v_start, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
THEN
v_count := 1;
ELSIF v_start < v_end
THEN
IF TO_CHAR(v_date, 'Dy') = v_saturday_abbrv
THEN
v_count := v_count + 1;
v_date := v_date + 1;
END IF;
IF v_date <= v_end AND TO_CHAR(v_date, 'Dy') = v_sunday_abbrv
THEN
v_count := v_count + 1;
v_date := v_date + 1;
END IF;
v_start_sunday := NEXT_DAY(v_date - 7, v_sunday_abbrv);
v_count := v_count + (v_end_sunday - v_start_sunday) / 7 * 2;
IF TO_CHAR(v_end, 'Dy') = v_saturday_abbrv
THEN
v_count := v_count + 1;
END IF;
END IF;
RETURN v_count;
END;
--------------------------------------------------------------------------------
Test1: 10-JUN-15 11.24.38.666404 AM 10-JUN-15 11.24.41.749738 AM 3.083334
Test2: 10-JUN-15 11.24.41.749738 AM 10-JUN-15 11.24.42.401349 AM .651611
Test1 took: 4.73186302870884622880829206382335473158 times as long as Test2
--------------------------------------------------------------------------------