SQL Server Function to get Number Of Days in a month.

Here is an example to getting number of days in a month.

CREATE FUNCTION dbo.GetNumberOfDays
(
@inMonth DATETIME
)
RETURNS INT
AS
BEGIN
Declare @Days int
Set @Days=0
set @Days=CASE WHEN MONTH(@inMonth) IN (1,3,5,7,8,10,12) THEN 31
WHEN MONTH(@inMonth) IN (4,6,9,11) THEN 30

ELSE CASE WHEN (YEAR(@inMonth) % 4 = 0 AND YEAR(@inMonth) % 100!=0) OR (YEAR(@inMonth) % 400=0) THEN 29
ELSE 28 END
END
Return
(
select @Days
);
END

PRINT dbo.GetNumberOfDays(getDate())

...S.VinothkumaR.

1 comment:

Imran said...

this is very usefull... but can you provide function to "Get Number Of BUSINESS Days in a Month". like to exclude 2 days off every week