PLSQL function to find first sunday in month.

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;
/

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.

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??

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;
 

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;
/

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;

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;
/
 
 

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 ;

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.

for more flexibility i added the weekdays parameter, i will remove it for you

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;
/

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;
/

Select allOpen in new window

:)

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;
/

Is ur function returning the right out put for 1-jan-10 ?

Yes, it should return false, and it dose. :)

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;
/

sorry , imean for
'01-AUG-10' which is first sunday

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

To shajukg.

Thank you, i'm not at work beacouse it is weekend, i will try it on monday.