How to determine the number of days in a month in SQL Server?

Most elegant solution: works for any @DATE

DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@DATE),0)))

Throw it in a function or just use it inline. This answers the original question without all the extra junk in the other answers.

examples for dates from other answers:

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'1/31/2009'),0))) Returns 31

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2404-feb-15'),0))) Returns 29

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2011-12-22'),0))) Returns 31

In SQL Server 2012 you can use EOMONTH (Transact-SQL) to get the last day of the month and then you can use DAY (Transact-SQL) to get the number of days in the month.

DECLARE @ADate DATETIME

SET @ADate = GETDATE()

SELECT DAY(EOMONTH(@ADate)) AS DaysInMonth

You can use the following with the first day of the specified month:

datediff(day, @date, dateadd(month, 1, @date))

To make it work for every date:

datediff(day, dateadd(day, 1-day(@date), @date),
              dateadd(month, 1, dateadd(day, 1-day(@date), @date)))