| 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
|
|
Related Topics:
|
|
|