oracle 如何使用查询获取两个给定日期之间的月份列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6926923/
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
How to get a list of months between 2 given dates using a query?
提问by Nitish
I have 2 dates, say 28-Mar-2011 and 29-Jun-2011. I need an sql query that will display the months between these 2 dates including the months containing the dates, ie. June, May, April and March.
我有 2 个日期，比如 28-Mar-2011 和 29-Jun-2011。我需要一个 sql 查询来显示这两个日期之间的月份，包括包含日期的月份，即。六月、五月、四月和三月。
回答by Justin Cave
Something like this
像这样的东西
SQL> ed
Wrote file afiedt.buf
select to_char( add_months( start_date, level-1 ), 'fmMonth' )
from (select date '2011-03-30' start_date,
date '2011-06-29' end_date
from dual)
connect by level <= months_between(
trunc(end_date,'MM'),
trunc(start_date,'MM') )
* + 1
SQL> /
TO_CHAR(ADD_MONTHS(START_DATE,LEVEL-
------------------------------------
March
April
May
June
should work.
应该管用。
回答by Reimius
Gonna add this solution just because I think it's much cleaner than the others:
要添加这个解决方案只是因为我认为它比其他解决方案更干净：
SELECT ADD_MONTHS(TRUNC(TO_DATE('28-Mar-2011', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) date_out
FROM DUAL
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('28-Mar-2011', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1)
<= TRUNC(TO_DATE('29-Jun-2011', 'DD-MON-YYYY'), 'MON')
回答by bruno
You can use the function MONTHS_BETWEEN
您可以使用函数MONTHS_BETWEEN
SELECT MOD( TRUNC( MONTHS_BETWEEN( '2011-07-29', '2011-03-28' ) ), 12 ) as MONTHS
FROM DUAL
Output
输出
MONTHS
----------
4
回答by Rubenisme
I needed an answer to this a couple of days ago. I found another solution I liked more:
几天前我需要一个答案。我找到了另一个我更喜欢的解决方案：
select to_char(which_month, 'Mon-yyyy') month
from
(
select
add_months(to_date(:start_date,'mm-yyyy'), rownum-1) which_month
from
all_objects
where
rownum <= months_between(to_date(:end_date,'mm-yyyy'), add_months(to_date(:start_date,'mm-yyyy'), -1))
order by
which_month
)
You could of course use any format you want. I 'union'ed and summed over another set so that I'd get the months even when they didn't have results.
您当然可以使用任何您想要的格式。我“联合”并总结了另一组，这样即使他们没有结果，我也能得到几个月的时间。
回答by Ahmed Elshayeb
SELECT MIN (to_date((TO_CHAR (Actual_Date, 'DD-MM-RRRR')),'dd-mm-rrrr')) F_DATE,
MAX (to_date((TO_CHAR (Actual_Date, 'DD-MM-RRRR')),'dd-mm-rrrr')) T_DATE,
TO_CHAR (Actual_Date, 'MM-RRRR') TRX_MONTH
FROM ( SELECT TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr')) + LEVEL - 1
Actual_Date
FROM (SELECT TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr'), 'MM') - 1
AS dt
FROM DUAL)
CONNECT BY LEVEL <=
( TO_DATE (:P_TDATE, 'dd-mm-rrrr')
- TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr'))
+ 1))
GROUP BY TO_CHAR (Actual_Date, 'MM-RRRR')
ORDER BY 1
回答by Bruno Blaugrana Kucevic
declare
v_date_from_first_day date;
v_date_to_last_day date;
v_month_name varchar2(10);
v_month_number number;
v_year_number number;
v_month_diff number;
begin
v_date_to_last_day := to_date('31.12.2018');
v_date_from_first_day := to_date('01.01.2018');
select months_between(v_date_to_last_day,v_date_from_first_day) as diff into v_month_diff from dual;
for i in 1..round(v_month_diff, 2) loop
select
to_char(trunc(add_months(v_date_to_last_day - months_between(v_date_from_first_day, v_date_to_last_day), -i)), 'fmMonth') as month_nm,
to_char(trunc(add_months(v_date_to_last_day - months_between(v_date_from_first_day, v_date_to_last_day), -i)), 'MM') as month_num,
to_char(trunc(add_months(v_date_to_last_day - months_between(v_date_from_first_day, v_date_to_last_day), -i)), 'YYYY') as year_num
into v_month_name, v_month_number, v_year_number
from dual;
dbms_output.put_line(v_month_number || '/' || v_year_number);
dbms_output.put_line(v_month_name || '/' || v_year_number);
end loop;
end;
Output:
12/2018
11/2018
10/2018
9/2018
8/2018
7/2018
6/2018
5/2018
4/2018
3/2018
2/2018
1/2018
Here, month names are in Croatian
在这里，月份名称是克罗地亚语
Prosinac/2018
Studeni/2018
Listopad/2018
Rujan/2018
Kolovoz/2018
Srpanj/2018
Lipanj/2018
Svibanj/2018
Travanj/2018
O?ujak/2018
Velja?a/2018
Sije?anj/2018
