SQL Query to generate al the fiscal years going forward

Question

I need to write SQL query that will give me all the fiscal years going forward 10/01/2015. So for now it should return 2016, after 10/01/2016, the query should return 2016, 2017. Same as after 10/01/2017, the query should return 2016,2017, 2018. I am having tough time coming up with any logic for this Query.I need to put the query as a parameter for SSRS report. Any help is appreciated. Thank you so much

Answer 1

You can generate a list, specifying a start and end using a recursive query like this:

with cte(YR) 
as(select cast('10/1/2016' as date) as YR
   union all
   Select dateadd(year, 1, YR)
   from cte
   where YR < cast('10/1/2020' as date)
)    

select * from cte;

Answer 2

So this is what I came up with based on Mo's Suggestion.

declare @curMonth int
set @curMonth = datepart(month, getdate()); 

with cte(YR)
as(select cast('10/1/2016' as date) as YR
   union all
   Select dateadd(year, 1, YR)
   from cte
   where YR < case 
        when @curmonth >= 10
        then dateadd(year, 1, getdate())
        else cast(getdate() as date)
        end
)    

select datepart(year,YR) as YR from cte;

Thank you Mo2 and John Cappeliti for your answers.