Oracle如何列出两个日期之间的月份的最后几天 [英] Oracle How to list last days of months between 2 dates

问题描述

我设法获得2个日期之间的所有日期.但我想获得两个日期之间的所有月份的最后一天(使用一个请求).

I manage to get all the days between 2 dates. But I would like to get all the lasts day of months between 2 dates (using one request).

两个日期之间的所有天:

All days between 2 dates:

 select to_date('01/01/2000','dd/mm/yyyy') + (LEVEL-1) as jour
 from dual
 connect by level <= to_date('31/12/2050','dd/mm/yyyy')-to_date('01/01/2000','dd/mm/yyyy')

当月最后一天:

 select  LAST_DAY(sysdate)  FROM dual 

我不知道如何将两者混合并获得预期的结果:

I don't know how to mix both and get the expected result:

20000131
20000228
20000331
etc...

我想，

推荐答案

那应该是 DISTINCT + LAST_DAY .

设置日期格式(以使其与您的日期格式匹配；或者，将 TO_CHAR 应用于具有适当格式掩码的 jour 值):

Setting date format (so that it matches yours; alternatively, apply TO_CHAR to the jour value with appropriate format mask):

SQL> alter session set nls_Date_format = 'yyyymmdd';

Session altered.

我缩短了时间跨度至2年(以节省空间:)).

I shortened time span to 2 years (to save space :)).

SQL> select distinct last_day(to_date('01/01/2000','dd/mm/yyyy') + (LEVEL-1)) as jour
  2  from dual
  3  connect by level <= to_date('31/12/2002','dd/mm/yyyy')-to_date('01/01/2000','dd/mm/yyyy')
  4  order by 1;

JOUR
--------
20000131
20000229
20000331
20000430
20000531
20000630
20000731
20000831
<snip>
20020630
20020731
20020831
20020930
20021031
20021130
20021231

36 rows selected.

SQL>

这篇关于Oracle如何列出两个日期之间的月份的最后几天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！