CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperplsqldeveloperoraclereports
aws
aws

Reputation: 83

count Sunday in plsql between two dates :date1 and :date2

I know there is lot of work for this on forums but i tried many things but get error please I have two parameters in oracle reports :date1 and :date2 I want to check sunday and then return gives me how many Sundays in these two dates

function SUNDAY_CFormula return NUMBER is
start_date DATE := :DATE1;
end_date DATE := :DATE2;
A NUMBER;
begin
SELECT Count(*) 
FROM   (SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday')INTO A 
        FROM DUAL;   
        CONNECT BY LEVEL <= end_date - start_date + 1) 
WHERE  A IN ( 'sunday' );
RETURN A;
end;

Upvotes: 0

Views: 1435

Answers (2)

Belayer
Belayer

Reputation: 14861

As an alternative. I always try creating formulas for date range process rather than "iterating", I just do not like generating data to just throw it away. Yes, it is sometimes necessary, but not in this case. The following will accomplish what you want:

create or replace 
function sunday_calc ( date1_in  date
                     , date2_in  date 
                     , sun_in    varchar2 default 'sun'
                     ) 
  return number 
is
    sun_count integer; 
begin
    with date_range( start_date, end_date) as
         ( select trunc(least(date1_in,date2_in))
                , trunc(greatest(date1_in,date2_in))
             from dual
         ) 
    select floor((trunc(end_date) - trunc(next_day(start_date-1,sun_in))/7)) + 1
      into sun_count 
      from date_range;
    return sun_count;
end sunday_calc;

Note: Unfortunately the next_day function does not accept a NLS_DATE_LANGUAGE parameter, so I created a substitute. Sun_in parameter: include the name in target language corresponding to English day 'Sunday'

Curious about this compared to the function by @MDO I ran some tests on each. And they produced the same result; Except in some instances where the start date was greater that the end date there was a difference of 1. Comparing to actual calendar the formula was correct (see fiddle). But why, MDO's logic seems completely sound. At that point I just had to know why. Took awhile but there is a slight bug in her/his code. Turns out when the start date is greater then the end date their routine actually began looking at dates for the greatest date and moved forward. Thus changing the period look at the the greater of the dates to that date plus number of days. This is corrected by applying the least function to the "Select to_char(start_date...", results in:

create or replace 
function sunday_cformula_r (date1 date, date2 date) return number is
start_date date := date1;
end_date date := date2;
a number;
begin
select count(*) into a
from   (select to_char(least(end_date, start_date) + ( level - 1 ), 'fmday', 'nls_date_language = english') a
        from dual 
        connect by level <= greatest(end_date, start_date) - least(end_date, start_date) + 1
 
        ) t
where  t.a in ( 'sunday' );
return a;
end;

Upvotes: 0

Mahamoutou
Mahamoutou

Reputation: 1555

You could rewrite your function like below. It is safer to add 'nls_date_language = english' clause in your to_char function in order to make your function independent from your default environnment settings.

create or replace 
function SUNDAY_CFormula (DATE1 date, DATE2 date) return NUMBER is
start_date DATE := DATE1;
end_date DATE := DATE2;
A NUMBER;
begin
SELECT Count(*) INTO A
FROM   (
       SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday', 'nls_date_language = english') A
        FROM DUAL 
        CONNECT BY LEVEL <= end_date - start_date + 1
        ) t
WHERE  t.A IN ( 'sunday' );
RETURN A;
end;
/

You could even use below version to make your function more flexible about the two dates it takes as parameters, no matter if date1 is greater or less than date2.

create or replace 
function SUNDAY_CFormula (DATE1 date, DATE2 date) return NUMBER is
start_date DATE := DATE1;
end_date DATE := DATE2;
A NUMBER;
begin
SELECT Count(*) INTO A
FROM   (SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday', 'nls_date_language = english') A
        FROM DUAL 
        CONNECT BY LEVEL <= greatest(end_date, start_date) - least(end_date, start_date) + 1
        ) t
WHERE  t.A IN ( 'sunday' );
RETURN A;
end;
/

Upvotes: 1

Related Questions