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.