# How to get all the passed months in the current year.

ex;In the current year if the current month is November want to get all the passed months
(1,2,3,4,5,6,7,8,9,10)  same for Feb to Dec

In the current year if the current month is Jan want to get all the Months from previous year.

Thanks
###### Who is Participating?

Commented:
BTW, if you need the month numbers instead of month names, modify the cte like this:
``````declare @startDate as datetime = '2013-01-01'

;with cte (StartDt, prevMonth, Month)
as (
union all
from cte
where dateDiff(month, prevMonth, @startDate) < 12 and datepart(month,prevMonth) > 1
)
select Month from cte
``````
0

Commented:
This recursive cte will return you the desired result:
``````declare @startDate as datetime = '2013-01-01'

;with cte (StartDt, prevMonth, Month)
as (
union all
from cte
where dateDiff(month, prevMonth, @startDate) < 12 and datepart(month,prevMonth) > 1
)
select Month from cte
``````

Result for datetime = '2013-01-01':
``````|     MONTH |
-------------|--
|  December |
|  November |
|   October |
| September |
|    August |
|      July |
|      June |
|       May |
|     April |
|     March |
|  February |
|   January |
``````

Result for datetime = '2013-09-01':
``````|    MONTH |
------------|--
|   August |
|     July |
|     June |
|      May |
|    April |
|    March |
| February |
|  January |
``````

SQL Fiddle
0

Commented:

for the above CTE approaches, if using getdate()
try this as the first line

declare @startDate as datetime = DATEADD(dd, - (DAY(getdate()) - 1), convert(date,getdate()))

(it will force @startDate to be the first of the current month, so all dates calculated will be the first of a month)
0

Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.