Get Number of Days in Month (SQL Function-UDF)

Following User Defined Function (UDF) returns the numbers of days in month. It is very simple yet very powerful and full proof UDF.

CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth]
( @myDateTime DATETIME )RETURNS INT AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0 AND YEAR(@myDateTime) % 100 != 0) OR (YEAR (@myDateTime) % 400 = 0) THEN 29
ELSE 28 END
END
RETURN @rtDate
END
GO




Run following script in Query Editor:

SELECT dbo.udf_GetNumDaysInMonth(GETDATE()) -- Today = 13.02.2009

Result :

28