PL/SQL How to get X day ago from a Date as Date?
20,850
Solution 1
Assumption was made that the 08-APR-13
is a string in your situation. So you need convert it to date
using to_date
function, and then simply subtract 100 literal.
SQL
SQL> select (to_date('08-APR-13', 'DD-MON-RR') - 100) res 2 from dual 3 / RES ----------- 29-12-2012
PL/SQL
SQL> declare 2 l_res_date date; 3 l_in_date varchar2(11) := '08-APR-13'; 4 begin 5 select (to_date(l_in_date, 'DD-MON-RR') - 100) 6 into l_res_date 7 from dual; 8 9 dbms_output.put_line(to_char(l_res_date, 'dd-mon-yy')); 10 end; 11 / 29-dec-12 PL/SQL procedure successfully completed
OR
SQL> declare
2 l_res_date date;
3 l_in_date varchar2(11) := '08-APR-13';
4 begin
5
6 l_res_date := to_date(l_in_date, 'DD-MON-RR') - 100;
7
8 dbms_output.put_line(to_char(l_res_date, 'dd-mon-yy'));
9 end;
10 /
29-dec-12
PL/SQL procedure successfully completed
Solution 2
this can be done select query by just minus as Nicholas Krasnov said.. IN pl/sql by creating a function DATE_AGO whose input parameter is date and number of days you need to subtract...
create or replace function DATE_AGO(DATE1 date, NUMBER_DAYS number) return date
is
V_DATE1 date:=DATE1;
v_NUMBER_DAYS number:=NUMBER_DAYS;
begin
V_DATE1:=V_DATE1-v_NUMBER_DAYS;
return V_DATE1;
when OTHERS then
DBMS_OUTPUT.PUT_LINE(SQLCODE);
end DATE_AGO;
/
SHOW ERRORS;
select DATE_AGO('08-APR-13',100) from DUAL;
Comments
-
yetAnotherSE about 4 years
I want to get 100 days ago from 08-APR-13, as date.
How to do it with pl/sql?