## 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