Find the number of days in a month

— Set @InputDate to the first day of the month you want to find the length of.

declare @InputDate datetime
declare @OutputDate datetime
declare @Output int

set @InputDate = ‘2009-05-01’

set @InputDate = dateadd(month, 1, @InputDate)
set @OutputDate = cast((cast((datepart(year, @InputDate)) as nvarchar(4)) + ‘-‘ +
 cast((datepart(month, @InputDate)) as nvarchar(2)) + ‘-01’) as datetime)
set @Output = datepart(day, (dateadd(s, -1, @OutputDate)))

select @Output

go