generate date range per year basis

Question

I want to generate date range between trunc('7/1/2014','mm/dd/yyyy') and trunc(sysdate-1)+0.99999 (from 7/1/2014 till yesterday midnight) per year basis.

please refer to the attached image for expected result (https://i.stack.imgur.com/UD4Ub.png)

Answer 1

Something like this. Adapt as needed. You probably won't select * from ranges but instead you will use the ranges wherever/however you need them. The input date dt, selected from a table input_date in my solution, may instead be a bind variable in your application, etc. Hope you are able to figure out the adjustments yourself; if not, please write back.

with
     input_date ( dt ) as (
       select to_date('07/01/2014', 'mm/dd/yyyy')
       from   dual
     ),
     ranges ( date_from, date_to ) as (
       select add_months(dt, 12 * (level - 1)) + level - 1,
              least(trunc(sysdate), add_months(dt, 12 * level) + level - 1)
       from   input_date
       connect by add_months(dt, 12 * (level - 1)) + level - 1 <= trunc(sysdate)
     )
select * from ranges
;

DATE_FROM   DATE_TO
----------  ----------
07/01/2014  07/01/2015
07/02/2015  07/02/2016
07/03/2016  11/28/2016