SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function

Note: Tomorrow is February 29th. This blog post is dedicated to coming tomorrow – a special day :)

Q: “How can I find leap year in using SQL Server 2012?“

There are many functions written to figure out to figure out if any year is Leap Year or not. The same I have written using T-SQL function over here.

CREATE FUNCTION dbo.IsLeapYear (@year INT)RETURNS INT
AS
BEGIN
RETURN(IIF(DATEPART(dd,(EOMONTH(CONCAT(@year,'0201')))) = 29,1,0))ENDGO

 

You can validate above query by running following script.

SELECT dbo.IsLeapYear('2011') 'IsLeapYear';SELECT dbo.IsLeapYear('2012') 'IsLeapYear';GO


 

You will get result 1 if the year is leap year and 0 if year is not leap year.

Here is the article Pinal Dave has written which introduces all the new functions in SQL Server 2012 Summary of All the Analytic Functions – MSDN and SQLAuthority and 14 New Functions – A Quick Guide.