Thursday, January 21, 2021

Conditionally date in oracle PL/SQL Function

create or replace FUNCTION f_get_start_date ( p_date date ) return date

is

v date;

begin

null;

if to_char(P_DATE,'dd') between 16 and 31 then

SELECT (TRUNC(TO_DATE(P_DATE,'DD-MON-RR'),'MON')+15) INTO V FROM DUAL;

else

SELECT ADD_MONTHS(TRUNC(TO_DATE(P_DATE,'DD-MON-RR'),'MON')+15,-1) INTO V FROM DUAL;

end if;

return v;

end;

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

create or replace FUNCTION       f_get_end_date ( p_date date ) return date

is 
v date;

begin

null;

if  to_char(p_date,'dd') between 01 and 15 then 


 SELECT (TRUNC(TO_DATE(P_DATE,'DD-MON-RR'),'MON')+15)-1 INTO V FROM DUAL;
 
else 

 SELECT (ADD_MONTHS(TRUNC(TO_DATE(P_DATE,'DD-MON-RR'),'MON')+14,+1)) INTO V FROM DUAL;

end if;

return v;

end;


select devapp.test_get_start_date('30-JUN-21') dd, devapp.test_get_end_date('30-JUN-21') ddd from dual;


SELECT MAX(TRUNC(TO_DATE('10-FEB-21','DD-MON-RR'),'MON')+15),MAX(ADD_MONTHS(TRUNC(TO_DATE('10-FEB-21','DD-MON-RR'),'MON')+14,+1)) FROM DUAL;

SELECT (TRUNC(TO_DATE('10-FEB-21','DD-MON-RR'),'MON')+15),(ADD_MONTHS(TRUNC(TO_DATE('10-FEB-21','DD-MON-RR'),'MON')+14,+1)) FROM DUAL;

No comments:

Post a Comment

To generate a PDF using JavaScript in Oracle APEX from a collection

  To generate a PDF using JavaScript in Oracle APEX from a collection, you can follow these steps: 1. Create a button or link on your APEX p...