BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

A SQL Server Leap Year Test Function

Transact-SQL does not provide a standard function that allows developers to determine if a year is a leap year. This article describes two functions. One to calculate the number of days in a year and one that utilises the result to identify leap years.

DateTime Data Type

The standard SQL Server DateTime data type allows the storage of date and time values between 1 January 1753 and the end of 31 December 9999 with an accuracy of a fraction of a second. Transact-SQL (T-SQL), the scripting language for SQL Server, does not include a means to easily identify if the year portion of a DateTime value represents a leap year. In this article we will create such a function that you can use in your projects.

The rules for identifying a leap year are simple. If the final two digits of the year are not both zero and the year is divisible by four without remainder, it is a leap year with 366 days. If the last two digits are both zero and the year divides exactly by 400, it is a leap year. All other years are 365 days in length. We could use this knowledge to determine if a year is a leap year. However, as the rules for calendars are built into the DateTime data type it is more sensible to reuse their functionality, making use of the DateAdd and DateDiff functions.

We'll create two scalar functions in this article. The first uses the standard DateTime functions to calculate the number of days in a given year. The second will use the result of the first to generate a Bit value that is true, or one, for leap years and false, or zero, otherwise.

DaysInYear Function

The function that calculates the number of days in a year is shown below. It accepts an integer containing the year to process and returns an integer holding the number of days in the year. We start by checking that the year is within the valid range for a DateTime, excluding the maximum year of 9999. This is necessary as we use the year to create DateTime values and they must remain within range to avoid errors. If the year is not within the valid range the function returns -1.

Once the year is validated we create a DateTime object that holds 1 January for that year. As SQL Server's date zero represents 1 January 1900, we calculate the date by subtracting 1900 from the year parameter and adding the resultant number of years to date zero. This is held in the variable @Jan1. Next, we add one year to this value to obtain a DateTime containing 1 January for the following year and store it in @NextJan1.

Finally, the DateDiff function is used to calculate the number of days between the two dates. This is the number of days in the year and is the return value.

CREATE FUNCTION DaysInYear (@Year INT)
RETURNS INT
AS
BEGIN
    DECLARE @Jan1 DATETIME
    DECLARE @NextJan1 DATETIME

    IF @Year < 1753 OR @Year > 9998
        RETURN -1

    SET @Jan1 = dateadd(yyyy, @Year-1900, 0)
    SET @NextJan1 = dateadd(yyyy, 1, @Jan1)
    RETURN datediff(d, @Jan1, @NextJan1)
END

The following script shows the results for several years.

PRINT dbo.DaysInYear(2012)  -- 366
PRINT dbo.DaysInYear(2011)  -- 365
PRINT dbo.DaysInYear(1752)  -- -1

IsLeapYear Function

The IsLeapYear function accepts an integer containing the year to be checked and returns a Bit. The function calls DaysInYear to obtain the number of days in the provided year. If this is 366, the year is a leap year and the function returns true (1). All other years return false (0).

CREATE FUNCTION IsLeapYear (@Year INT)
RETURNS BIT
AS
BEGIN
    DECLARE @IsLeapYear BIT
	
    IF dbo.DaysInYear(@Year) = 366
        SET @IsLeapYear = 1
    ELSE
        SET @IsLeapYear = 0
	
    RETURN @IsLeapYear
END

The script below shows some sample results. Note that years outside of the valid range generate a zero result. In most cases this should not cause a problem. If it is a problem, you can modify the DaysInYear function to add or subtract multiples of 400 to the year to force it to be within the valid date range and obtain the correct values.

PRINT dbo.IsLeapYear(2012)  -- 1
PRINT dbo.IsLeapYear(2011)  -- 0
PRINT dbo.IsLeapYear(1752)  -- 0
29 January 2012