Tip of the Day : SQL Server Database Design - Twitter Profile and Followers
Determine Leap Year Function

Determine Leap Year Function

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 leap year. 

CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate    DATETIME )
RETURNS BIT
AS
BEGIN

    IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
        YEAR( @pDate ) % 400 = 0
        RETURN 1

    RETURN 0

END
GO

Description

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.

Usage

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.