SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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
Code

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
Code

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
Code

SQL Server leap year function



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.