| 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.
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:
|
Based on these conditions, the following user-defined function returns the number of days in a month for a given input date:
1) GET Month Count...
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
2) GET First Day in Month…
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
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.
3) Get Year Count…
CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate DATETIME )
RETURNS BIT
AS
BEGIN
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
RETURN 1
RETURN 0
END
