CODEWITHSUNDEEP
sqlsql-server
S.Das
S.Das

Reputation: 15

SQL query to get list of months till now by entering year

I have a requirement where I have to display all the months of present year till now when year parameter is entered.

For example if I enter 2016, then all months till September should be shown.

How to achieve this? Any ideas would be highly appreciated.

Upvotes: 1

Views: 5457

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

This is easy with a recursive CTE:

with m as (
      select datefromparts(year(getdate()), 1, 1) as mm
      union all
      select dateadd(month, 1, m.mm)
      from m
      where m.mm < getdate()
     )
select m.*
from mm;

It is unclear to me what the parameter is used for. This gets the months from this year.

Upvotes: 3

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Use the below script.

;with months (date)
AS
(
    SELECT cast(@year+'-01-01' as date)
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where  DATEADD(month,1,date)<= case when @year=YEAR(getdate()) THEN CAST(getdate() as date) ELSE cast(@year+'-12-31' as date) END
)
select Datename(month,date) [Month] from months

Output :

enter image description here

Upvotes: 1

Related Questions