SQL Server Portal

I was working on statistical reports and I had to display whether the given year was leap year or not in a field. So, I developed few ways using SQL SERVER 2012 functions to detect whether leap year or not.

Given below are the multiple ways to detect leap year:

Method 1 :
In method 1, I made a date (2012-02-28) using DATEFROMPARTS function then used EOMONTH function to find the last date (2012-02-29) of February. Then to find the last day (29), used DAY function and after that used IIF to check whether it is 29 or not, to detect if its a leap year or not.

DECLARE @Year INT =2012
SELECT IIF(DAY(EOMONTH(DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 2 :
In method 2, I simply made 2 dates using DATEFROMPARTS function. First is the 1st date of the year and 2nd date is the last date of the year and then used DATEDIFF function to find total no of days in a year and finally used IIF function to check if it is 366 or 365 days. If 366, then leap year and if 365 then it is not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,1,1), DATEFROMPARTS(@year,12,31))+1=366 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 3 :
In method 3, I first made a date(2012-02-28) using DATEFROMPARTS function then added one day to it. After addition I took the day and checked it using IIF function. If it is 29, then leap year, else, it is not.

DECLARE @Year INT =2012
SELECT IIF(DAY(DATEADD(DAY,1,DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Method 4 :
In method 4, I made 2 dates (2012-02-28, 2012-03-01) using DATEFROMPARTS function and then found the date difference using DATEDIFF function, and finally used IIF function to check the difference. If it is 2 days, means the year is leap, else not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,2,28), DATEFROMPARTS(@year,3,01))=2 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Note : Try @Year as 2013 and all the result set will return that 2013 is not a leap year using any above methods.

Let me know if you know a better method to detect leap year.