使用设定值返回日期列表 [英] Return list of date using a set value

问题描述

如何从日期中减去几个月以产生不同的日期?

How can I subtract a number of months from a date, to produce a different date?

1. var date_A = 2016年6月24日
2. var date_B = 2016年1月24日
3. var x = 5

请问如何使用x(月数)中的值从date_A值计算date_B的值?

Please how can I use the value in x (number of months) to calculate the value for date_B, from the value date_A?

推荐答案

您可以使用

You can use the add_months() function:

select add_months(to_date('24-06-2016', 'DD-MM-YYYY'), -5) from dual;

ADD_MONTHS(TO_DATE('24-06-2016','DD-MM-YYYY'),-6)
-------------------------------------------------
2015-12-24                                       

使用SQL * Plus或SQL Developer绑定变量(不确定这是否是您所拥有的)，您可以执行以下操作:

With SQL*Plus or SQL Developer bind variables (not sure if that's what you have) you can do:

var date_a varchar2(10);
var date_b varchar2(10);
var x number;

exec :date_a := '24-06-2016';
exec :x := 5;

exec :date_b := to_char(add_months(to_date(:date_a, 'DD-MM-YYYY'), -:x), 'DD-MM-YYYY');

print date_b

DATE_B
------
24-01-2016

如果要使用前五个月中的每个月和当前月份，则可以使用虚拟

if you want each of the previous five months, and the current month, you can use a dummy hierarchical query:

select add_months(to_date('24-06-2016', 'DD-MM-YYYY'), 1-level)
from dual
connect by level <= 6;

ADD_MONTHS(TO_DATE('24-06-2016','DD-MM-YYYY'),1-LEVEL)
------------------------------------------------------
2016-06-24                                            
2016-05-24                                            
2016-04-24                                            
2016-03-24                                            
2016-02-24                                            
2016-01-24                                            

或者，如果您实际上有开始和结束日期，而不是结束日期和x，则可以执行以下操作:

Or if you actually have the start and end date, rather than the end date and x, you can do:

select add_months(to_date('24-06-2016', 'DD-MM-YYYY'), 1-level)
from dual
connect by add_months(to_date('24-06-2016', 'DD-MM-YYYY'), 1-level)
  >= to_date('24-01-2016', 'DD-MM-YYYY');

ADD_MONTHS(TO_DATE('24-06-2016','DD-MM-YYYY'),1-LEVEL)
------------------------------------------------------
2016-06-24                                            
2016-05-24                                            
2016-04-24                                            
2016-03-24                                            
2016-02-24                                            
2016-01-24                                            

或者您可以使用递归子查询分解，而不是connect by语法(如果愿意)，并且您使用的是11gR2或更高版本:

Or you could use recursive subquery factoring instead of the connect by syntax if you prefer, and you're on 11gR2 or higher:

with r (dt) as (
  select to_date('24-06-2016', 'DD-MM-YYYY') from dual
  union all
  select add_months(r.dt, -1) from r
  where add_months(r.dt, -1) >= to_date('24-01-2016', 'DD-MM-YYYY')
)
select r.dt
from r
order by r.dt desc;

您也可以在这些查询中为日期或上限使用绑定变量.

You can use bind variables for the dates or upper limit in those queries too.

这篇关于使用设定值返回日期列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！