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'))