PLSQL function to find first sunday in month.

Hi Experts,

I need a PL/SQL function, which for a given date can tell whether the date is First or Third Sunday in the month.
If so should return TRUE (boolean), otherwise FALSE.
delmandiyarAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you will need these things:

SYSDATE returns the current date+time.

TRUNC(your_date, 'MM') to give you the date of the first day of the month

NEXT_DAY(some_date, 'SUNDAY')   to give you the next sunday as from that date

some_Date + 14  to add up 2 weeks (for the third)

this are all the "bricks" you need

0
 
shru_0409Connect With a Mentor Commented:
CREATE OR REPLACE FUNCTION f_find_sunday(
   a_date DATE
)
   RETURN BOOLEAN
IS
   CURSOR c1
   IS
       SELECT     ROWNUM nbr, LEAST(NEXT_DAY(CASE  WHEN LEVEL = 1  THEN TRUNC(d, 'mm') - 1  ELSE   NEXT_DAY(TRUNC(d, 'mm'),'Monday')
             + (7 *(LEVEL - 2)) END,'Sunday'),TRUNC(LAST_DAY(d))) sun_date
      FROM (SELECT sysdate d FROM DUAL)
      CONNECT BY NEXT_DAY(TRUNC(d, 'mm'), 'Monday') +(7 *(LEVEL - 2)) <= LAST_DAY(d);

   l_rtn BOOLEAN;
   l_date DATE;
 
BEGIN
   FOR r1 IN c1         
   LOOP
      l_date := TRUNC(r1.sun_date);
        
      IF l_date = TRUNC(a_date)
      THEN
         IF r1.nbr = 1 OR r1.nbr = 3
         THEN
            l_rtn := TRUE;
                  exit;
         ELSE
            l_rtn := FALSE;
         END IF;
        ELSE
               l_rtn := FALSE;
      END IF;
        
   END LOOP;  

   RETURN l_rtn;
END;

try this
0
 
Shaju KumbalathDeputy General Manager - ITCommented:

CREATE OR REPLACE function SHAJU.f_get_1or3_dayofweek(indate in date,inday in varchar2) return boolean as

cursor date_cur is
SELECT dt FROM(select trunc(indate,'mm') -1 + days Dt,TRIM(TO_CHAR(trunc(indate,'mm')-1 + days,'DAY')) as DAYS,TO_CHAR(trunc(indate,'mm') -1 + days,'W') as WEEk from (
select rownum days from all_objects where rownum <= 31)) WHERE WEEK IN (1,3) AND DAYS=inday;
v_istrue boolean:=FALSE;
begin
for myrec in date_cur
loop


v_istrue:= CASE WHEN TRIM(MYREC.DT)=TRIM(INDATE) THEN
TRUE
ELSE
FALSE END;

IF v_istrue THEN
EXIT;
END IF;
end loop;
return v_istrue;
end;
/
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Shaju KumbalathDeputy General Manager - ITCommented:
Note : we can not  use the function in sql as it is return BOOLEAN datatype;
BOOLEAN is a PL/SQL datatype and NOT a SQL datatype. IT is not available when writing SQL statements.
0
 
delmandiyarAuthor Commented:
Thank you all for the answers, i'm working on it.

shajukg: Do you mean if i run the function like this:

select function(parameter) from dual;

will not work??
0
 
Shaju KumbalathDeputy General Manager - ITCommented:

Example of execution:
 

DECLARE
a BOOLEAN;
dt DATE := TO_DATE ('08-JAN-10', 'DD-MON-YY');
wday VARCHAR2 (10) := 'SUNDAY';
BEGIN
a := f_get_1or3_dayofweek (dt, wday);
IF a
THEN
DBMS_OUTPUT.put_line (TO_CHAR (dt, 'DD-MON-YY') || ' TRUE');
ELSE
DBMS_OUTPUT.put_line (TO_CHAR (dt, 'DD-MON-YY') || ' FALSE');
END IF;
END;
 
0
 
delmandiyarAuthor Commented:
Her is my function, why it's not working?

create or replace function find_sunday (date_in varchar2)
return boolean is

my_date date;

cursor c1 is
select next_day(trunc(to_date(date_in), 'MM'),'SUNDAY') first_sun from dual;

begin
open c1;
fetch c1 into my_date;

if my_date = date_in then
RETURN TRUE;
else
return false;
end if;
close c1;
end;
/
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
yes... sql doesn't support bolean data type , but u can use this function in pl/sql block
or u can change the return data type from bollean to some other type;
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
this works for me... try it out...

create or replace function check_1_3_sunday(d1 date) return number
is
dummy number:=0;
begin

select 1
into dummy
from
(
select trunc(dd1) dd1, myday, row_number() over ( partition by to_char(dd1,'mm') order by dd1) rr
from
(select trunc(d1,'mm') + level - 1 dd1,  to_char(trunc(d1,'mm')+level-1,'DY') myday
from dual
connect by level <= to_char(add_months(trunc(d1,'mm'),1)-1,'dd') )
where myday='SUN'
)
where rr in (1,3)
and dd1 = trunc(d1);

return dummy;

exception
when no_data_found then
return dummy;

end;
/

select check_1_3_sunday('03-jan-2010') from dual; --> this will return 1 means TRUE
select check_1_3_sunday('10-jan-2010') from dual; --> this will return 0 means FALSE
select check_1_3_sunday('17-jan-2010') from dual; --> this will return 1 means TRUE
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
if u want to use the function part of SQL statement convert the function like this

CREATE OR REPLACE function SHAJU.f_get_1or3_dayofweek2(indate in date,inday in varchar2) return varchar2 as
cursor date_cur is
SELECT cast(dt as date) dt FROM(select TRIM(trunc(indate,'mm') -1 + days) Dt,TRIM(TO_CHAR(trunc(indate,'mm')-1 + days,'DAY')) as DAYS,TO_CHAR(trunc(indate,'mm') -1 + days,'W') as WEEk from (
select rownum days from all_objects where rownum <= 31)) WHERE WEEK IN (1,3) AND DAYS=inday;
v_yesno varchar2(3):='NO';
begin
for myrec in date_cur
loop


v_yesno:= CASE WHEN MYREC.DT=INDATE THEN
'YES'
ELSE
'NO' END;
 

IF v_yesno='YES' THEN
EXIT;
END IF;
end loop;
return v_yesno;
end;
/
 
 
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
SORRY I LEFT OUT SOME THING

CREATE OR REPLACE function f_get_1or3_dayofweek2(indate in date,inday in varchar2) return varchar2 as
cursor date_cur is
SELECT cast(dt as date) dt FROM(select TRIM(trunc(indate,'mm') -1 + days) Dt,TRIM(TO_CHAR(trunc(indate,'mm')-1 + days,'DAY')) as DAYS,TO_CHAR(trunc(indate,'mm') -1 + days,'W') as WEEk from (
select rownum days from all_objects where rownum <= 31)) WHERE WEEK IN (1,3) AND DAYS=inday;
v_yesno varchar2(3):='NO';
begin
for myrec in date_cur
loop


v_yesno:= CASE WHEN MYREC.DT=TRIM(INDATE) THEN
'YES'
ELSE
'NO' END;
 

IF v_yesno='YES' THEN
EXIT;
END IF;
end loop;
return v_yesno;
end;
/
 

SELECT f_get_1or3_dayofweek2(SYSDATE,'SUNDAY') FROM DUAL ;
0
 
delmandiyarAuthor Commented:
shajukg

i found out that i can tset it with anonymous PLSQL block like u did, but your function have 2 parameters, i need only one and it should be a date.
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
for more flexibility i added the weekdays parameter, i will remove it for you
0
 
Shaju KumbalathDeputy General Manager - ITCommented:


CREATE OR REPLACE function SHAJU.f_get_1or3_dayofweek2(indate in date) return varchar2 as
cursor date_cur is
SELECT cast(dt as date) dt FROM(select TRIM(trunc(indate,'mm') -1 + days) Dt,TRIM(TO_CHAR(trunc(indate,'mm')-1 + days,'DAY')) as DAYS,TO_CHAR(trunc(indate,'mm') -1 + days,'W') as WEEk from (
select rownum days from all_objects where rownum <= 31)) WHERE WEEK IN (1,3) AND DAYS='SUNDAY';
v_yesno varchar2(3):='NO';
 
begin
for myrec in date_cur
loop


v_yesno:= CASE WHEN MYREC.DT=TRIM(INDATE) THEN
'YES'
ELSE
'NO' END;
 

IF v_yesno='YES' THEN
EXIT;
END IF;
end loop;
return v_yesno;
end;
/
0
 
delmandiyarAuthor Commented:
this returns varchar, the first one you wrote returns boolean, if u could remove the second parameter from there, will be nice.

i attached the code, so you know which one i'm talking about?
CREATE OR REPLACE function SHAJU.f_get_1or3_dayofweek(indate in date,inday in varchar2) return boolean as

cursor date_cur is
SELECT dt FROM(select trunc(indate,'mm') -1 + days Dt,TRIM(TO_CHAR(trunc(indate,'mm')-1 + days,'DAY')) as DAYS,TO_CHAR(trunc(indate,'mm') -1 + days,'W') as WEEk from (
select rownum days from all_objects where rownum <= 31)) WHERE WEEK IN (1,3) AND DAYS=inday;
v_istrue boolean:=FALSE;
begin
for myrec in date_cur
loop


v_istrue:= CASE WHEN TRIM(MYREC.DT)=TRIM(INDATE) THEN
TRUE
ELSE
FALSE END;

IF v_istrue THEN
EXIT;
END IF;
end loop;
return v_istrue;
end;
/

Open in new window

0
 
shru_0409Connect With a Mentor Commented:
create or replace function find_sunday1 (date_in varchar2)
return boolean is

my_date date;
l_count number(22);
l_indate date;

cursor c1 is
select next_day(trunc(to_date(sysdate), 'MM'),'SUNDAY') first_sun from dual;

BEGIN
      
 l_indate := to_date(trim(date_in));
   OPEN c1;
   LOOP
      FETCH c1 INTO my_date;       
      EXIT WHEN c1%NOTFOUND;
        
          SELECT to_number(to_char(my_date,'J'))- to_number(to_char(l_indate,'J'))
        INTO l_count
        FROM dual;

        IF l_count = 0 THEN
               RETURN TRUE;
        ELSE
               RETURN FALSE;
        END IF;
                      
       /*      if my_date = l_indate  then
               RETURN TRUE;
            else
                  return false;
            end if;
        */      
   END loop;
   CLOSE c1;
END;


try thi your function....
0
 
delmandiyarAuthor Commented:
:)

Thank you all, at the end, i did it in my way, and it's very very simple :D.

so i divide the points.

create or replace function find_sunday (date_in varchar2)
return boolean is

my_date date;

begin
if next_day(trunc(to_date(date_in), 'MM'),'SUNDAY') = date_in or next_day(trunc(to_date(date_in), 'MM'),'SUNDAY')+14 =date_in then
RETURN TRUE;
else
return false;
end if;
end;
/
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
Is ur function returning the right out put for 1-jan-10 ?
0
 
delmandiyarAuthor Commented:
Yes, it should return false, and it dose. :)
0
 
delmandiyarAuthor Commented:
and i test my function like this:

declare
mydate date := '11-01-02';
begin

if find_sunday(mydate) then

dbms_output.put_line('true');

else

dbms_output.put_line('false');
end if;
end;
/
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
sorry , imean for
'01-AUG-10' which is first sunday
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
if next_day(trunc(to_date(date_in), 'MM'),'SUNDAY') = date_in or next_day(trunc(to_date(date_in), 'MM'),'SUNDAY')+14 ;
i think there is a correction required in your function,
it should be
next_day(trunc(to_date(date_in), 'MM')-1,'SUNDAY')  = date_in or next_day(trunc(to_date(date_in), 'MM'),'SUNDAY')+14 ;
Because if when first day of the month falls as 'SUNDAY' then
next_day(trunc(to_date(date_in), 'MM'),'SUNDAY')  with return 2nd Sunday
Next_Day always returns first day later than give date
0
 
delmandiyarAuthor Commented:
To shajukg.

Thank you, i'm not at work beacouse it is weekend, i will try it on monday.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.