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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

SQL Server leap year function







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







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems