The combination of DATEDIFF and DATEADD, by the way, doesn't always work. If you put a date of 1/31/2009 into it, the DATEADD will return 2/28/2009 and the DATEDIFF gives you 28, rather than 31.
–
Stan ScottMar 27 '09 at 19:05

SELECT Datediff(day,
(Convert(DateTime,Convert(varchar(2),Month(getdate()))+'/01/'+Convert(varchar(4),Year(getdate())))),
(Convert(DateTime,Convert(varchar(2),Month(getdate())+1)+'/01/'+Convert(varchar(4),Year(getdate()))))) as [No.of Days in a Month]

DECLARE @date date = '2011-12-22'
/* FindLastDayOfMonth - Find what is the last day of a month - Leap year is handled by DATEADD */
-- Get the first day of next month and remove a day from it using DATEADD
DECLARE @lastDayOfMonth date = CAST( DATEADD(dd, -1, DATEADD(mm, 1, FindFirstDayOfMonth(@date))) AS date)
SELECT @lastDayOfMonth

Those could be combine to create a single function to retrieve the number of days in a month if needed.

How it works: subtracting the date's day number from the date itself gives you the last day of previous month. So, you need to add one month to the given date, subtract the day number and get the day component of the result.