There will be times when you will need to determine the number of days in a
year. This can be the case when you need to compute for the interest of
savings accounts in banks. This should be simple enough because it is
either 365 days if it is not a leap year and 366 if it is a leap year.
Now the problem is determining if a year is a leap year or not.
Currently, there is no date function within SQL Server that determines if a
year is a leap year or not.
The user-defined function below determines whether a particular date is of a
leap year or not. It takes a DATETIME parameter and return a bit flag,
which determines if the date is within a leap year or not. A return value
of 1 means that it is a leap year while a return value of 0 means it is not a
CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate DATETIME )
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
The first part of the script simply checks if the user-defined function already
exists in the current database or not. If it already exists, it is
dropped so that the creation of the function will not fail.
The main body of the function determines if it is a leap year or not.
Basically, 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.
Given this, year 1990, although divisible by 4, is not a leap year because it is
not divisible by 100. On the other hand, year 2000 is a leap year because
it is divisible by 400.
Here's an example on how to use this user-defined function.
DECLARE @vDaysInYear INT
SET @vDaysInYear = 365 + [dbo].[ufn_IsLeapYear] ( GETDATE() )
This example gets the number of days in a year by adding the result of the
[dbo].[ufn_IsLeapYear] to 365. If it is leap year, the function returns 1
and is added to 365, which gives the number of days in a leap year.