This user defined functions return the number of days in a given month.
ALTER FUNCTION GetDaysCountInMonth(
@Date DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @count INT
SET @count = CASE
WHEN DATEPART(MM,@Date) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN DATEPART(MM,@Date) IN (4, 6, 9, 11) THEN 30
ELSE
CASE WHEN (DATEPART(YY,@Date) % 4 = 0
AND
DATEPART(YY,@Date) % 100 != 0)
OR
((DATEPART(YY,@Date)) % 400 = 0)
THEN 29
ELSE 28
END
END
RETURN @count
END
GO
--Result
SELECT dbo.GetDaysCountInMonth(GETDATE()) COUNT
SELECT dbo.GetDaysCountInMonth('2000-02-12')
COUNT--Leap year
SELECT dbo.GetDaysCountInMonth('2100-02-12') COUNT
