Get year calendar with months name and number of days in sql server

In this article I am going to explain how to get year calendar with months name and number of days in sql server.

In the previous article I have explained how to validate corporate or businessemail address in asp.net using RegularExpression, how to show and hide thepassword on checkbox click using Jquery in asp.net and how to get monthcalendar with week day name in sql server.

Description:

I want to get the year calendar i.e. month name with how many days in a month.

Implementation:

Run the below given query in Sql server to get the year calendar:

declare @StartDate DATE

declare @Enddate DATE

set @StartDate = '2016-01-01'

set @Enddate   = '2016-12-31'

 

;with months (date)

AS

(

    SELECT @StartDate

    UNION ALL

    SELECT DATEADD(month,1,date)

    from months

    where DATEADD(month,1,date)<=@Enddate

)

select DATEPART(mm ,Date) as [Month Number],DATENAME(mm ,Date) as [Month Name]            

            ,DATEPART(dd,DATEADD(day,-1,DATEADD(MONTH,+1,date))) as [Number of Days In Month]

            ,DATEPART(yy ,Date) as [Year] from months