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).
