SQL SERVER – UDF – User Defined Function – Get Number of Days in Month
User Defined Function 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 ENDEND RETURN @rtDate END GO
ResultSet:
NumDaysInMonth
———————–
30
Thanks to Pinal Dave
————————————————————————————————
There is another way to get the same result.
CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth]
(
@myDateTime DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = DATEPART(dd, DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@myDateTime)+1, 0)))
RETURN @rtDate
END
GO
ResultSet:
NumDaysInMonth
———————–
30