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: SQL Server, SQL Server 2005
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: SQL Server, SQL Server 2005
Comments
DateTime.DaysInMonth(Now.Year, Now.Month)
That will return the days in the month.
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.
declare @dtDate datetime
set @dtDate ='20070201'
Select day(dateadd(month,datediff(month,-1,@dtDate),-1))
http://sqlblogcasts.com/blogs/madhivanan