Wednesday, January 03, 2007

How to find the number of days in a month

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

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


Technorati tags: ,

3 comments:

Nick Dresang said...

in .net vb you can use

DateTime.DaysInMonth(Now.Year, Now.Month)

That will return the days in the month.

Vadivel said...

Thanks for stopping by!

True we can do it in frontend as well. But the guy who actually asked this question to me was expecting it to be done in DB level.

Madhivanan said...

Other way is


declare @dtDate datetime
set @dtDate ='20070201'
Select day(dateadd(month,datediff(month,-1,@dtDate),-1))

http://sqlblogcasts.com/blogs/madhivanan