Simple way to determine the number of days in a month for any given date

In our regular development process we sometime require the number of days in a particular month. What will you do for this? There are several methods to get this. The one method is given below:-

DECLARE @varDate datetime

DECLARE @varMonthDate datetime

DECLARE @varYear datetime

DECLARE @varFOM varchar(10)

DECLARE @NumDaysInMonth as int

select @varDate = '2/1/2009'

select @varMonthDate = MONTH(@varDate)

select @varYear = YEAR(@varDate)

select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +

CAST(CAST(@varYear as int) as varchar)

select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))

select @NumDaysInMonth as DaysInMonth

I think this is very awkward method to get days of a month. We wrote many lines to get a particular result. I am thankful to my colleague Amit Pawar that discovered a simplest query to get days in a particular month. The query he wrote is as follows:-

Select DATENAME(DAY,DATEADD(DAY,-1,DATEADD(Month,1,'February 2009')))

This select query returns number of days in a particular month. You can put date in "MM/DD/YYYY" or "MM/DD/YY" format also.

There is an another version of Using SQL statement or SQL Query to calculating the Number of Days in a Month

select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))

Example on May (month), you just copy and paste the SQL Query or SQL statement to the SQL Query Analyzer. You will get the answer is 31 days for May(month).