Oracle查询指定时间区间内的月份

1.查询今年以来的月份

SQL如下：

Select To_number(To_char(Add_months(Trunc(sysdate, 'yy'),Rownum - 1),'MM')) As month 
From Dual 
Connect By Rownum <= (
        Select Months_between(Trunc(sysdate, 'mm'),
               Trunc(sysdate, 'yy'))+1
        From Dual
) 

结果：

 

2.查询指定时间区间内（ 20230117 - 20230817 ）的每个月末

SQL如下：

Select Rownum,To_char(Add_months(Last_day(To_date(20230117,'yyyy-mm-dd')),Rownum - 1),'yyyymmdd') As monEnd 
From Dual 
Connect By Rownum <= (
        Select Months_between(To_date(20230817,'yyyy-mm-dd'),
               To_date(20230117,'yyyy-mm-dd'))+1
        From Dual
) 

结果：

 

3.查询指定时间区间内的每个月末并转化为区间

SQL如下：

With time_temp As(
  Select Add_months(To_date(20230117,'yyyy-mm-dd'),- 2) As begindate,
         To_date(20230817,'yyyy-mm-dd') As enddate
  From Dual
)
,time_month As(
  Select Rownum As rn,To_char(Add_months(Last_day((Select begindate From time_temp)),Rownum - 1),'yyyymmdd') As monEnd 
  From Dual 
  Connect By Rownum <= (
        Select Months_between((Select enddate From time_temp),
               (Select begindate From time_temp))+1
        From Dual
  )   
)
Select a.monend As begindate,b.monend As enddate
From time_month a
Inner Join time_month b On b.rn = a.rn+1

结果：

实际工作里，要求每个月末的数据与上个月末进行对比，考虑到 月末不一定有数据，所以需将每个月转换为时间段，然后查找该时间段内的最后一条数据即可。