SQL Calculate Days in Month

There is no option available in SQL to calculate days of a specific month, so it is very poor approach to write CASE statement in every single T-SQL.

USER DEFINED FUNCTION is the right way of doing these kind of tasks which have to be done on regular basis.

CALL:
select dbo.GetDaysinmonth(5)

OUTPUT:
31

T-SQL for Calculate Days in Month Function

USE [TESTDB]
GO
/****** Object:  UserDefinedFunction [dbo].[GetDaysInMonth]    Date: 01/31/2012 16:04:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetDaysInMonth] ( @pDate    DATETIME )
RETURNS INT
AS
BEGIN

    RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND
                                YEAR(@pDate) % 100 != 0) OR
                               (YEAR(@pDate) % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END

END


Enjoy!!