
Getting the number of days in a year is fairly easy because you are just
choosing between 365 and 366, with the latter only happening every 4 years or
every leap year. To determine if it is a leap year, either of the
following conditions must be met:

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

The year must be divisible by 400.
Below is a userdefined function which accepts a date as a parameter and returns
the number of days in that year.

CREATE FUNCTION [dbo].[ufn_GetDaysInYear] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @IsLeapYear BIT
SET @IsLeapYear = 0
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
SET @IsLeapYear = 1
RETURN 365 + @IsLeapYear
END
GO
Description
The function above first determines if it is a leap year using the conditions
mentioned above. If it is a leap, a bit flag is set to 1, otherwise it
remains 0 as initially set. Then this bit flag is added to 365 and is
returned by the function.
Determining if a year is a leap year or not can also be useful in other
situations so it is best to create a separate userdefined function for this
and simply calling this function within the [dbo].[ufn_GetDaysInYear] function
above. The following code assumes that a userdefined function called
[dbo].[ufn_IsLeapYear] exists which accepts a date as a parameter and
returns a value of 1 if it is a leap year or a value 0 if it is not.
CREATE FUNCTION [dbo].[ufn_GetDaysInYear] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN 365 + [dbo].[ufn_IsLeapYear] ( @pDate )
END
GO
