This seems to be one another frequently asked question in the discussion forums. So thought would write a small post on this today.
With the help of built in SQL Server functions we can easily achieve this in one single T-SQL statement as shown below.
Select Day(DateAdd(Month, 1, '01/01/2007') - Day(DateAdd(Month, 1, '02/01/2007')))
We can generalize it by creating a "User defined Stored Procedure" as shown below:
Create Function NumDaysInMonth (@dtDate datetime) returns int
Return(Select Day(DateAdd(Month, 1, @dtDate) - Day(DateAdd(Month, 1, @dtDate))))
Technorati tags: SQL Server, SQL Server 2005