Tip of the Day : SQL Server Database Design - Twitter Profile and Followers
Get Number of Days in a Month Function

Get Number of Days in a Month Function

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 (Non-Leap Year) - 28 Days
  • February (Leap Year) - 29 Days

The only tricky part here is determining whether it is a leap year or not.  Basically, a year is a leap year if one of the following conditions are met:

  • The year must be divisible by 4 and must NOT be divisible by 100.
  • The year must be divisible by 400.

Based on these conditions, the following user-defined function returns the number of days in a month for a given input date:

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

Description

The user-defined function is quite straight-forward.  It uses a CASE statement to return the appropriate number of days based on the month of the input date.  It returns 31 for the months January (1), March (3), May (5), July (7), August (8), October (10) and December (12).  It returns 30 for the months of April (4), June (6), September (9) and November (11).  If it is a leap year, it returns 29 for the month of February (2), otherwise it returns 28.

The process of determining if it is a leap year or not given an input date can be very useful in other functions such as a function that will determine the number of days in a year.  It would be a good idea to make it a separate user-defined function by itself and simply calling it from the [dbo].[ufn_GetDaysInMonth] function.  The following code assumes that a user-defined function called [dbo].[ufn_IsLeapYear] exists which accepts a date as a parameter and returns an integer value of 1 if it is a leap year or a value 0 if it is not. 

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 [dbo].[ufn_IsLeapYear] ( @pDate ) + 28
           END

END
GO

Variations

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 based on the difference between the first day of the current month and the first day of the following month of a given input date.

CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate    DATETIME )
RETURNS INT
AS
BEGIN

    SET @pDate = CONVERT(VARCHAR(10), @pDate, 101)
    SET @pDate = @pDate - DAY(@pDate) + 1

    RETURN DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate))
END
GO

Description

To get the number of days in a given month, what this variation of the user-defined function is doing is simply determine the first day of the month for the given input date then get the difference between the first day of the current month and the first day of the following month.

  1. SET @pDate = CONVERT(VARCHAR(10), @pDate, 111) - Since the input parameter is defined as a DATETIME data type, it may or may not contain a time part.  To be sure that the function is only dealing with the date part of the input parameter, it is converted to a VARCHAR(10) data type with a format of "YYYY/MM/DD", as specified in the third parameter of the CONVERT function (111).  There is no need to CAST or CONVERT this VARCHAR representation of the input date back to DATETIME data type because the receiving variable is of DATETIME data type already and SQL Server will perform an implicit conversion.
  2. SET @pDate = @pDate - DAY(@pDate) + 1 - This part of the function gets the first day of the month for the given input date.  This is achieved by simply subtracting the DAY part of the input date from itself then adding 1.  So for example if the given date is '07/24/2002', the day part of this date is subtracted from it, which is 24, which will give you '06/30/2002'.  Then 1 is added to it to make it '07/01/2002' to make it the first day of the month for the given date.

    This step is similar to the following code:

    SET @pDate = DATEADD(DD, 1 - DAY(@pDate), @pDate)

    Not many are aware that adding integer values to or subtracting integer values from DATETIME data types is simply adding or subtracting days to it.
  3. RETURN DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate)) - The last step in the function is to return the difference between the first day of the current month and the first day of the following month.  To get the first day of the following month, the DATEADD function is used, and to get the difference between the first days, the DATEDIFF function is used.

Word of Caution

In determining the number of days in a month, you may wonder why the variation of the user-defined function above have to convert the input date to the first day of the month and not just simply get the difference between the input date and one month from the input date.  Why not just use DATEADD to add 1 month to the input date then use DATEDIFF to get the difference between these 2 dates?

Let's take the sample input date above, '07/24/2002'.  If we add one month to this date using the DATEADD function, we will get '08/24/2002'.  Using the DATEDIFF function to get the difference between these 2 dates, we will get 31, which is the number of days in July.

This may be the case for the sample date but this logic will give incorrect results for certain dates, specifically dates at the end of the month.  Let's take '01/31/2002' as an example.  If we apply the same logic to this date as above, we will be adding one month to this using the DATEADD function, which will give us '02/28/2002'.  The next step is to get the difference between these two dates using the DATEDIFF function, which will give us 28, which is incorrect because January has 31 days.

The extra step of getting the first day of the month avoids this problem with the DATEADD function where the parameter being passed is for adding months.  If the date is the first day of the month, the DATEADD function with a parameter of MM will always give us the first day of the following month.  Unlike in the case of the last day of the month, the DATEADD function with a parameter of MM will also give us the last day of the following month instead of adding the number of days in the current month.