Calculating the days of the month?

Hi everyone,
Can anyone tell me how I could find out the number of days that there are
in any given month using TSQL?
I was hoping there was function I could use?
Any help would be much appreciated
Simon"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b25e6f8c80ce0f3cef08d@.news.microsoft.com...
> Hi everyone,
> Can anyone tell me how I could find out the number of days that there are
> in any given month using TSQL?
> I was hoping there was function I could use?
> Any help would be much appreciated
> Simon
Hi Simon,
Easily done using a calendar table.
http://www.aspfaq.com/show.asp?id=2519|||you could use something like this
declare @.d datetime
select @.d = getdate()
select datename(m,@.d) , datediff(d,@.d,dateadd(m,1,@.d))
so for example for today (March, current month) we could do this:
select datename(m,getdate()) ,
datediff(d,getdate(),dateadd(m,1,getdate
()))
or for February 2006 you can use this
declare @.d datetime
select @.d = '20060201'
select datename(m,@.D) , datediff(d,@.d,dateadd(m,1,@.d))
http://sqlservercode.blogspot.com/|||scrap that, that won't work unless you use the first day of the month
declare @.d datetime
select @.d = '20060130'
select datename(m,@.D) , datediff(d,@.d,dateadd(m,1,@.d)) -- this will
give a wrong result
this is fine
declare @.d datetime
select @.d = '20060101'
select datename(m,@.D) , datediff(d,@.d,dateadd(m,1,@.d))
http://sqlservercode.blogspot.com/|||CREATE FUNCTION dbo.fDaysOfMonth(@.dtFecha DATETIME)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, @.dtFecha) + 1, 0)))
END
GO
SELECT dbo.fDaysOfMonth('2006-02-03')
28