How To Get Number of Days in Month

1. Query

-----code:start-----------------------------------------------

CREATE FUNCTION [dbo].[GetDaysInMonth] ( @day DATETIME )
RETURNS INT
AS
BEGIN

RETURN
CASE
WHEN MONTH(@day) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@day) IN (4, 6, 9, 11) THEN 30

ELSE
CASE WHEN (YEAR(@day) % 4 = 0
AND
--Leap Year
YEAR(@day) % 100 != 0)
OR (YEAR(@day) % 400 = 0)
THEN 29
ELSE 28
END
END

END



GO

-----code:end-----------------------------------------------


2. A. Try to run the function

-----code:start-----------------------------------------------
select dbo.GetDaysInMonth( '9/1/2012')
-----code:end-----------------------------------------------

Results:
30


2. B. 
-----code:start-----------------------------------------------
select dbo.GetDaysInMonth( '12/1/2012')
-----code:end-----------------------------------------------

Results:
31


3. Optional: To remove the function

-----code:start-----------------------------------------------
DROP FUNCTION [GetDaysInMonth]
-----code:end-----------------------------------------------


Source:
http://www.sqlservercentral.com/scripts/Date+Manipulation/73152/
Article: Function to get number of days in month