how to get Number of days of current Month in sql server?

Question

Abhay Shanker · Answer

Declare @dt datetime set @dt = getdate() select DT = @dt [Days in Month] =day(dateadd(mmdatediff(mm-1@dt)-1))

Ankur Jain · Answer

CREATE FUNCTION [dbo].[ufn_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 GO