[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PLSQL function to find first sunday in month.

Posted on 2010-01-08
23
Medium Priority
?
3,113 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:delmandiyar
  • 11
  • 8
  • 2
  • +2
23 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 26207814
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
 
LVL 14

Assisted Solution

by:shru_0409
shru_0409 earned 400 total points
ID: 26208154
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26208172

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26208339
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
 

Author Comment

by:delmandiyar
ID: 26208478
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26208539

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
 

Author Comment

by:delmandiyar
ID: 26208547
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26208548
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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 100 total points
ID: 26208573
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26208646
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26208676
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
 

Author Comment

by:delmandiyar
ID: 26208718
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26208725
for more flexibility i added the weekdays parameter, i will remove it for you
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26208738


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
 

Author Comment

by:delmandiyar
ID: 26208767
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
 
LVL 14

Assisted Solution

by:shru_0409
shru_0409 earned 400 total points
ID: 26208769
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
 

Author Comment

by:delmandiyar
ID: 26209021
:)

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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26209103
Is ur function returning the right out put for 1-jan-10 ?
0
 

Author Comment

by:delmandiyar
ID: 26209124
Yes, it should return false, and it dose. :)
0
 

Author Comment

by:delmandiyar
ID: 26209131
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26209204
sorry , imean for
'01-AUG-10' which is first sunday
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26239857
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
 

Author Comment

by:delmandiyar
ID: 26272730
To shajukg.

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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question