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

score 58 · Accepted Answer · edited Nov 13 '15 at 15:37

58

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'...

edited Nov 13 '15 at 15:37

Adrian

207
2
13

answered Jun 30 '11 at 12:34

gbn

422,506
82
585
676

1

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.) – Kevin P. Rice May 03 '19 at 05:58

Kevin P. Rice · Answer 2 · 2019-05-03T05:53:11.030

10

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

edited May 03 '19 at 05:53

answered Dec 09 '18 at 22:44

Kevin P. Rice

5,550
4
33
39

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. – LucasM Jan 29 '20 at 16:43

Robert Koritnik · Answer 3 · 2011-06-30T12:42:28.167

4

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
;

edited Jun 30 '11 at 12:42

answered Jun 30 '11 at 12:35

Robert Koritnik

103,639
52
277
404

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. – Robert Koritnik Jun 30 '11 at 12:42

score 3 · Answer 4 · answered Oct 22 '14 at 11:21

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

score 1 · Answer 5 · answered May 19 '20 at 18:34

1

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

answered May 19 '20 at 18:34

Carlos P.

102
4

score 1 · Answer 6 · answered Dec 05 '20 at 12:56

1

3 line... but could be also 2...

DECLARE @Y as int = 2021;
DECLARE @Dt as char(10) = CAST(@Y as CHAR(4)) + '-02-29'
SELECT IIF(isDATE(@Dt) = 1, 1,0)

or

DECLARE @Dt as char(10) = '2020-02-29'
SELECT IIF(isDATE(@Dt) = 1, 1,0)

Alen

answered Dec 05 '20 at 12:56

ACaps

97
1
8

score 0 · Answer 7 · edited Jul 30 '13 at 08:50

0

I Have a better solution

CREATE FUNCTION dbo.IsLeapYear(@year INT)
RETURNS BIT AS  
     BEGIN
          DECLARE @d DATETIME,
                  @ans BIT      
          SET @d = CONVERT(DATETIME,'31/01/'+CONVERT(VARCHAR(4),@year),103)
          IF DATEPART(DAY,DATEADD(MONTH,1,@d))=29 SET @ans=1 ELSE SET @ans=0
          RETURN @ans
    END
GO

feel free to use

edited Jul 30 '13 at 08:50

Romesh

2,291
3
24
47

answered Jul 30 '13 at 08:30

user2633263

9
3

Sender · Answer 8 · 2022-06-07T04:08:28.003

-1

There are different way you can find.

DEMO

DECLARE @year INT = 2024;

-- Date Cast
SELECT @year AS [Year],
    CASE WHEN ISDATE(CAST(@year AS CHAR(4)) + '0229') = 1
        THEN 'Leap Year'
    ELSE 'Not a Leap Year' END

-- Year divisible by 4 but not by 100 OR year divisible by 400
SELECT @year AS [Year],
    CASE WHEN (@year % 4 = 0 AND @year % 100 <> 0) OR (@year % 400 = 0)
        THEN 'Leap Year'
    ELSE 'Not a Leap Year' END

-- Find Month
SELECT @year As [Year],
    CASE WHEN MONTH(DATEADD(D, 1, DATEFROMPARTS(@Year, 2, 28))) <> 3 
        THEN 'Leap Year'
    ELSE 'Not a Leap Year' END

-- A Leap Year has 366 days (the extra day is the 29th of February).
SELECT  @year As [Year],
    CASE WHEN DATEPART(dy,DATEFROMPARTS(@year,12,31)) = 366
        THEN 'Leap Year'
    ELSE 'Not a Leap Year' END

edited Jun 07 '22 at 04:08

answered Jun 06 '22 at 06:17

Sender

6,660
12
47
66

it seems to me like you just took all the other answers and copied them – t-clausen.dk Jun 06 '22 at 07:21
@t-clausen.dk How you feel like that? In my project, I need to find leap year, so I look different option and tested, what is best way to do this. If I look your approach That mean stack overflow all answer is copied from some ware. – Sender Jun 07 '22 at 04:22

Check for leap year

8 Answers8

Linked