SQL Server Helper - Get Number Of Days In A Year

Home > User-Defined Functions > Get Number of Days in a Year Function

Get Number of Days in a Year Function

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 user-defined 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 user-defined function for this and simply calling this function within the [dbo].[ufn_GetDaysInYear] function above. The following code assumes that a user-defined 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