获取日期范围的月末日期

如果你有一个以上的名字和一个日期,你可以这样做,但只有当输入的名字是不同的工作。(否则,可以修改查询以适应非唯一名称。)

奇怪的是,您希望调用月末日期 start_date ,与输入数据中相同。而且 name

with
     test_data ( name, start_date ) as (
       select 'Fly', to_date('01-Jul-16', 'dd-Mon-yy') from dual union all
       select 'Two', to_date('15-Feb-17', 'dd-Mon-yy') from dual
     )
-- End of simulated inputs (for testing only, not part of the solution).
-- Use your actual table and column names in the SQL below.
select     name, add_months(last_day(start_date), level - 1) as start_date
from       test_data
connect by add_months(last_day(start_date), level - 1) <= last_day(sysdate)
       and prior name = name
       and prior sys_guid() is not null
order by   name, start_date
;

输出

NAME  START_DATE
---   ----------
Fly   31-Jul-16 
Fly   31-Aug-16 
Fly   30-Sep-16 
Fly   31-Oct-16 
Fly   30-Nov-16 
Fly   31-Dec-16 
Fly   31-Jan-17 
Fly   28-Feb-17 
Fly   31-Mar-17 
Fly   30-Apr-17 
Fly   31-May-17 
Fly   30-Jun-17 
Fly   31-Jul-17 
Fly   31-Aug-17 
Two   28-Feb-17 
Two   31-Mar-17 
Two   30-Apr-17 
Two   31-May-17 
Two   30-Jun-17 
Two   31-Jul-17 
Two   31-Aug-17