Leap Year Function in SQL Server
SQL developers can use T-SQL functions to determine whether a year is a leap year or not.
It is easy to find leap years in SQL Server but first what is leap year and what is the rule to determine if a year is leap year.
The definition of Leap Year is best given at http://www.timeanddate.com/date/leapyear.html
To determine if a given year, there are 3 criterias to take into account in Gregorian calendar.
In general you can say that if the year is divisible by 4 with two exceptions.
First if the year is be divisible by 100, then it is not a leap year unless it can also be divided by 400.
If itcan be divided by 400 then we say that it is a leap year.
Below T-SQL developers will find the source codes of a leap year checking SQL function which can be used in all versions of SQL Server including SQL Server 2005, SQL Server 2012 and in SQL Server 2014. SQL function is_leap_year uses CASE statements with mod arithmetic function for controlling divisibility rules.
The input of the dbo.is_leap_year() function the year itself and the outcome is 1 (true) or 0 (false).
CREATE FUNCTION dbo.is_leap_year (
@date int
) RETURNS bit
BEGIN
RETURN
CASE
WHEN ( @date % 400 = 0)
THEN
1 -- Leap year
ELSE
CASE WHEN ( @date % 100 = 0 )
THEN 0 -- regular, not leap year
ELSE
CASE WHEN ( @date % 4 = 0 )
THEN 1 -- Leap year
ELSE 0 -- regular, not leap year
END
END
END
END
GO
Here is how dbo.is_leap_year function can be used to determine if a given year is a leap year or not with sample cases.
select dbo.is_leap_year(2014) -- 2014 is not a leap year
select dbo.is_leap_year(2016) -- 2016 is a leap year
select dbo.is_leap_year(2000) -- 2000 is a leap year
select dbo.is_leap_year(1900) -- 1900 is not a leap year
SQL Server 2012 has introduced new T-SQL functions for SQL programmers. Using these new SQL functions which are new with SQL Server 2012, like IIF, TRY_CONVERT and CONCAT developers can build following user defined SQL function to test whether a year is leap year or an ordinary year.
CREATE FUNCTION dbo.is_leap_year_SQL2012 (
@year INT
) RETURNS BIT
AS
BEGIN
return iif(try_convert(date, concat( Right(Replicate('0',4) + cast(@year as varchar(4)), 4) , '0229')) is null, 0, 1)
END
Or with a more readable SQL code for the above leap year checking function
CREATE FUNCTION dbo.is_leap_year_SQL2012 (
@year INT
) RETURNS BIT
AS
BEGIN
/*
return iif(try_convert(date, concat( Right(Replicate('0',4) + cast(@year as varchar(4)), 4) , '0229')) is null, 0, 1)
*/
declare @yearChar varchar(4) = @year
set @yearChar = Right(Replicate('0',4) + @yearChar, 4)
return iif(try_convert(date, concat(@yearChar, '0229')) is null, 0, 1)
END
