Oracle Query Date Calculation

1.



---SELECT---
DATE RANGE    DR
TODAY         TD
NEXT DAY      ND
7 DAYS        7D
15 DAYS       15D
30 DAYS       30D
THIS MONTH    TM
NEXT MOTH     NM


IF :CTRL_ROSTER.SCH_FR='DR' THEN
   :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
   :CTRL_ROSTER.TO_DATE:=NULL;
ELSIF :CTRL_ROSTER.SCH_FR='TD' THEN--TODAY
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE);
ELSIF :CTRL_ROSTER.SCH_FR='ND' THEN ----NEXT DAY
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE+1);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE+1);
ELSIF :CTRL_ROSTER.SCH_FR='7D' THEN--7 DAYS
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE+7);
ELSIF
  :CTRL_ROSTER.SCH_FR='15D' THEN--15DAYS
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE+15);
ELSIF
  :CTRL_ROSTER.SCH_FR='30D' THEN --30DAYS
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE+30);
ELSIF
  :CTRL_ROSTER.SCH_FR='TM' THEN--THIS MONTH
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=LAST_DAY(SYSDATE);
ELSIF :CTRL_ROSTER.SCH_FR='NM' THEN -- NEXT MONTH
    :CTRL_ROSTER.FROM_DT:=LAST_DAY(SYSDATE)+1;
    :CTRL_ROSTER.TO_DATE:=LAST_DAY(LAST_DAY(SYSDATE)+1);

END IF;
next_item;






2.



1st script
-------------
CREATE OR REPLACE Procedure PRC_DATECAL (pNoOfDays in Number,pThisMonth in varchar2,pNextMonth Varchar,pFrDate out date,pEnDate out Date)
     IS
     vDate Date;
     BEGIN
     if pNoOfDays is not null AND pThisMonth IS NULL and pNextMonth IS NULL then
     SELECT trunc(SYSDATE)
     INTO pFrDate
     FROM DUAL;
     SELECT trunc(SYSDATE+pNoOfDays)
     INTO pEnDate
     FROM DUAL;
     elsif  pNoOfDays is null AND pNextMonth IS NULL AND pThisMonth IS NOT NULL then
     SELECT trunc(SYSDATE)
     INTO pFrDate
     FROM DUAL;
     SELECT TRUNC(Last_Day(SYSDATE))
     INTO pEnDate
     FROM DUAL;
     elsif pNoOfDays is null AND pThisMonth IS NULL AND pNextMonth IS NOT NULL then
     SELECT TRUNC(Last_Day(SYSDATE)+1)
     INTO pFrDate
     FROM DUAL;
     SELECT TRUNC(LAST_DAY(Last_Day(SYSDATE)+1))
     INTO pEnDate
     FROM DUAL;
     end if;
   
exception when others then
null;
END;

PRC_DATECAL(










2nd Script
----------------------------
CREATE OR REPLACE FUNCTION FNC_DATECAL (pNoOfDays Number,p)
     RETURN date  IS
     vDate Date;
     BEGIN
     SELECT trunc(SYSDATE+pNoOfDays)
     INTO vDate
     FROM DUAL;
  return vDate;
END;
/





*********************************************************
Form plsql
............



---SELECT---
DATE RANGE    DR
TODAY         TD
NEXT DAY      ND
7 DAYS        7D
15 DAYS       15D
30 DAYS       30D
THIS MONTH    TM
NEXT MOTH     NM


IF :CTRL_ROSTER.SCH_FR='DR' THEN
   :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
   :CTRL_ROSTER.TO_DATE:=NULL;
ELSIF :CTRL_ROSTER.SCH_FR='TD' THEN--TODAY
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE);
ELSIF :CTRL_ROSTER.SCH_FR='ND' THEN ----NEXT DAY
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE+1);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE+1);
ELSIF :CTRL_ROSTER.SCH_FR='7D' THEN--7 DAYS
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE+7);
ELSIF
  :CTRL_ROSTER.SCH_FR='15D' THEN--15DAYS
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE+15);
ELSIF
  :CTRL_ROSTER.SCH_FR='30D' THEN --30DAYS
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=TRUNC(SYSDATE+30);
ELSIF
  :CTRL_ROSTER.SCH_FR='TM' THEN--THIS MONTH
  :CTRL_ROSTER.FROM_DT:=TRUNC(SYSDATE);
  :CTRL_ROSTER.TO_DATE:=LAST_DAY(SYSDATE);
ELSIF :CTRL_ROSTER.SCH_FR='NM' THEN -- NEXT MONTH
    :CTRL_ROSTER.FROM_DT:=LAST_DAY(SYSDATE)+1;
    :CTRL_ROSTER.TO_DATE:=LAST_DAY(LAST_DAY(SYSDATE)+1);

END IF;
next_item;




////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

CREATE OR REPLACE FUNCTION FNC_DATECAL (pNoOfDays Number,pLastDay varchar2)
     RETURN date  IS
     vDate Date;
     BEGIN
     if pNoOfDays is not null and pLastDay is null then
     SELECT trunc(SYSDATE+pNoOfDays)
     INTO vDate
     FROM DUAL;
     elsif pNoOfDays is null and pLastDay is not null then
     select trunc(last_day(sysdate))
     into vDate
     from dual;
     end if;
     /*
     Select trunc((sysdate)+pNoofMonth) into vDate
     from dual;
     */
  return vDate;
END;
/


select name,JOB_DESC
from hrv_empjobdetails
where emp_no=:global.g_emp_no
and nvl(ACTIVE_STATUS,'N')='Y'

hrv_empdetails


select FNC_DATECAL ('','htgt') from dual;