This article shows the ways to get the number of days in a month in both sqlserver and mysql.

Getting the number of days in a month is quite easy because it is going to be either 30 or 31 days, with the exception of February, which can either have 28 or 29 days depending if it is a leap year or not.

  • January,March,May,July,August,October,December — 31 Days
  • April,June,September,November – 30 Days
  • February – 28 Days (Non Leap Year), 29 (Leap Year)

Leap year should be divisible by 4, should not be divisible by 100 or should be divisible by 400.

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

Here’s yet another way of determining the number of days in a month without knowing if it is a leap year.  It is computed by getting the lastday of the month.

CREATE FUNCTION [dbo].[udf_GetDaysInMonth] ( @pDate    DATETIME )
RETURNS INT
AS
BEGIN
SET @pDate = @pDate - DAY(@pDate) + 1 -- Get the first date of the month
RETURN day(dateadd(dd,-1,@pDate))
END
GO

Get the number of days in month in MYSQL


select DAY(LAST_DAY('2004-02-05'))