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