Check for leap year

Question

How do I check if a year is a leap year?

I have this code:

declare @year int
set @year = 1968

SELECT CASE WHEN @YEAR = <LEAPYEAR> THEN 'LEAP YEAR' ELSE 'NORMAL YEAR' END

Expected result:

LEAP YEAR

Solution 1

Check for 29th Feb:

CASE WHEN ISDATE(CAST(@YEAR AS char(4)) + '0229') = 1 THEN 'LEAP YEAR' ELSE 'NORMAL YEAR' END

or use the following rule

CASE WHEN (@YEAR % 4 = 0 AND @YEAR % 100 <> 0) OR @YEAR % 400 = 0 THEN 'LEAP YEAR'...

Solution 2

MOST EFFICIENT LEAP YEAR TEST:

CASE WHEN @YEAR & 3 = 0 AND (@YEAR % 25 <> 0 OR @YEAR & 15 = 0) THEN ...

Adapted from: http://stackoverflow.com/a/11595914/3466415

Solution 3

Leap year calculation:

(@year % 4 = 0) and (@year % 100 != 0) or (@year % 400 = 0)

When this is true, then it is a leap year. Or to put it in case statement

select case when
    (
        (@year % 4 = 0) and (@year % 100 != 0) or
        (@year % 400 = 0)
    ) then 'LEAP' else 'USUAL' end
;

Solution 4

This could also help

DECLARE @year INT = 2012

SELECT IIF(DAY(EOMONTH(DATEFROMPARTS(@year,2,1))) = 29,1,0)  
Result: 1 --(1 if Leap Year, 0 if not)

SELECT IIF(DAY(EOMONTH(DATEFROMPARTS(@year,2,1))) = 29,'Leap year','Not Leap year')  
Result: Leap year 

Solution 5

Not sure how efficient this is compared to the other solutions. But is another option.

DECLARE @year int = 2016
SELECT CASE 
  WHEN DATEPART(dayofyear, DATEFROMPARTS(@year, 12, 31)) = 366
  THEN 'LEAP' 
  ELSE 'NOT LEAP' 
END

View more solutions

Comments

[Robert Koritnik]

updated one AND the same as you did in your updated answer. I was just testing it in SQL to prove it. So we kinda both answered with the same calculation.

[Alex]

first one is brilliant!

[Kevin P. Rice]

Indeed, the first one is clever; but there's a relatively large hidden computing cost. The second executes significantly faster. Below, I eliminate 2 of the 3 modulus operations, thus eliminating division which is slower than bitwise operations. (See "MOST EFFICIENT LEAP YEAR TEST", infra.)

[LucasM]

Please use this method as it checks on the full year rather than the accepted answer which initially is only check on the leap year day rather than the full year.