
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 (NonLeap 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 userdefined 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 userdefined function is quite straightforward. 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
userdefined function by itself and simply calling it from the
[dbo].[ufn_GetDaysInMonth] function. The following code assumes that a
userdefined 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
userdefined 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.

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.

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.

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 userdefined 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.
