Simple way to determine the number of days in a month for any given date

[Matt Karp]

DECLARE @varDate datetime
DECLARE @varMonthDate datetime
DECLARE @varYear datetime
DECLARE @varFOM varchar(10)
DECLARE @NumDaysInMonth as int

select @varDate = '2/1/2004'

select @varMonthDate = MONTH(@varDate)
select @varYear = YEAR(@varDate)
select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +
CAST(CAST(@varYear as int) as varchar)
select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))

select @NumDaysInMonth as DaysInMonth

[John Gilson]

"Matt Karp" <ma******@hotmail.com> wrote in message
news:f2*************************@posting.google.co m...

DECLARE @varDate datetime
DECLARE @varMonthDate datetime
DECLARE @varYear datetime
DECLARE @varFOM varchar(10)
DECLARE @NumDaysInMonth as int

select @varDate = '2/1/2004'

select @varMonthDate = MONTH(@varDate)
select @varYear = YEAR(@varDate)
select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +
CAST(CAST(@varYear as int) as varchar)
select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))

select @NumDaysInMonth as DaysInMonth

CREATE FUNCTION DaysInMonth (@d DATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT DAY(DATEADD(MONTH, 1, @d) -
DAY(DATEADD(MONTH, 1, @d)))
)
END

SELECT dbo.DaysInMonth('20040201') AS days

days
29

Regards,
jag