How to find the number of days in a month

.NET Classes used :

Introduction

This seems to be one another frequently asked question in the discussion forums. So thought would write a small article on this today.

Basic Solution

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

Generalized Solution:

We can generalize it by creating a "User defined Stored Procedure" as shown below:


Create Function NumDaysInMonth (@dtDate datetime) returns int
as
Begin
Return
(
Select Day(DateAdd(Month, 1, @dtDate) - Day(DateAdd(Month, 1, @dtDate)))
)
End
Go

Test:

Select dbo.NumDaysInMonth('20070201')
Go