S.Das S.Das - 2 months ago 6
SQL Question

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.

Answer

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

Comments