oracle PL/SQL 如何从日期中获取 X 天前的日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13508995/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL How to get X day ago from a Date as Date?
提问by yetAnotherSE
I want to get 100 days ago from 08-APR-13, as date.
我想从 08-APR-13 获得 100 天前的日期。
How to do it with pl/sql?
如何用 pl/sql 做到这一点？
采纳答案by Nick Krasnov
Assumption was made that the 08-APR-13is a string in your situation. So you need convert it to dateusing to_datefunction, and then simply subtract 100 literal.
假设08-APR-13您的情况是字符串。所以你需要将它转换为dateusingto_date函数，然后简单地减去 100 个文字。
SQL
SQL> select (to_date('08-APR-13', 'DD-MON-RR') - 100) res 2 from dual 3 / RES ----------- 29-12-2012PL/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
查询语句
SQL> select (to_date('08-APR-13', 'DD-MON-RR') - 100) res 2 from dual 3 / RES ----------- 29-12-2012PL/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
回答by GKV
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...
这可以通过 Nicholas Krasnov 所说的减法来完成选择查询.. IN pl/sql 通过创建一个函数 DATE_AGO 其输入参数是日期和需要减去的天数...
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;
