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.
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)
DECLARE @Jan1 DATETIME
DECLARE @NextJan1 DATETIME
IF @Year < 1753 OR @Year > 9998
SET @Jan1 = dateadd(yyyy, @Year-1900, 0)
SET @NextJan1 = dateadd(yyyy, 1, @Jan1)
RETURN datediff(d, @Jan1, @NextJan1)
The following script shows the results for several years.
PRINT dbo.DaysInYear(2012) -- 366
PRINT dbo.DaysInYear(2011) -- 365
PRINT dbo.DaysInYear(1752) -- -1
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)
DECLARE @IsLeapYear BIT
IF dbo.DaysInYear(@Year) = 366
SET @IsLeapYear = 1
SET @IsLeapYear = 0
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