SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function
- by pinaldave
Note: Tomorrow is February 29th. This blog post is dedicated to coming tomorrow – a special day :)
Subu: “How can I find leap year in using SQL Server 2012?“
Pinal: “Are you asking me how to year 2012 is leap year using T-SQL – search online and you will find many example of the same.”
Subu: “No. I am asking – How can I find leap year in using SQL Server 2012?“
Pinal: “Oh so you are asking – How can I find leap year in using SQL Server 2012?“
Subu: “Yeah - How can I find leap year in using SQL Server 2012?“
Pinal: “Let me do that for you – How can you find leap year in using SQL Server 2012?“
Indeed a fun conversation. Honestly, only reason I pasted our conversation here is – it was fun. What he was asking is that how to do it using new functions introduced in SQL Server 2012. Here is the article I have 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.
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))
END
GO
What I really like is that I was able to use three newly introduced function in SQL Server 2012 in above script. You can read more about them here. IIF, EOMONTH and CONCAT.
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.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
