Wednesday 16 December 2015

Dynamically pass the date or Year or month in pl/sql code in oracle

Code to Dynamically pass the date or Year or month in pl/sql code in oracle

Declare
 v_prev_year           VARCHAR2(10);
   v_prevdate            DATE;

    v_year                VARCHAR2 (2);

begin

SELECT TO_CHAR(ADD_MONTHS (SYSDATE, -12),'DD-MON-YY')--15-DEC-14
     INTO v_prevdate
     FROM DUAL;
   
  SELECT TO_CHAR(TO_DATE(v_prevdate,'DD-MON-YY'),'YY')
     INTO v_prev_year
     FROM DUAL;

SELECT TO_CHAR (v_prevdate, 'RR')
     INTO v_prev_year
     FROM DUAL;
   
  SELECT TO_CHAR (SYSDATE, 'RR')
     INTO v_year
     FROM DUAL;

SELECT column1,column2
    INTO ly2
    FROM table_name
    WHERE
TRUNC (creation_date) BETWEEN '01-FEB-' || v_prev_year AND '28-FEB-' || v_prev_year;
/*in this it dynamically passes the previous_year*/

SELECT column1,column2
    INTO ly2
    FROM table_name
    WHERE
TRUNC (creation_date) BETWEEN '01-FEB-' || v_yearAND '28-FEB-' || v_year;
/*in this it dynamically passes the year*/
end;

No comments:

Post a Comment