SQL Server and T-SQL Development Tutorials Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.
 Home Articles News IT Jobs Tools Sample Chapters Trainers Blogs Forums Photos Files

SQL Server 2019 Installation

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 ```

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums