Tuesday, February 28, 2012

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.
Post a Comment

Find a cool free stuff everyday

Giveaway of the Day

Hiren Bharadwa's Posts

DotNetJalps