SQL SERVER – Script to Find Leap Year

A leap year has 366 days. A leap year has 29 days for February month. Suppose you want to find if year is Leap year or not, you can use many methods. But this is one of the simplest methods. In our example I have passed the year 2000 as a variable to @year but you can change it to any year which you want to check for leap year.

DECLARE @YEAR SMALLINT
SET @YEAR=2000
SELECT @YEAR AS YEAR,
CASE
DAY
(EOMONTH(DATEADD(DAY,31,DATEADD(YEAR,@YEAR-1900,0))))
WHEN 29 THEN 'YES' ELSE 'NO'
END AS LEAP_YEAR
GO

The result is

YEAR LEAP_YEAR
 ------ ---------
 2000 YES

The logic is from the given year create a Date which results for Feb 01 of that year. You can do this by adding 31 (DATEADD(YEAR,@YEAR-1900,0) results for Jan 01 of that year). Using EOMONTH finds the last day of the month. Using DAY function find out day value. If it is 29, it is a leap year otherwise it is non Leap year.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Error: Fix for Error Msg 3906 – Failed to update database because the database is read-only
Next Post
SQL SERVER – The Basics of the Execute Package Task – Notes from the Field #067

Related Posts

No results found.

21 Comments. Leave new

  • For Pre SQL 2012….

    DECLARE @YEAR SMALLINT
    SET @YEAR=2000
    SELECT @YEAR AS YEAR,
    CASE
    DAY(DATEADD(dd,-1,DATEADD(mm,2,DATEADD(yy,@YEAR-1900,0))))
    WHEN 29 THEN ‘YES’ ELSE ‘NO’
    END
    AS LEAP_YEAR_OTHER

    GO

    Reply
  • DECLARE @DATE DATETIME
    SET @DATE=’2012-Feb-29′

    SELECT CASE (year(@date)%4)
    when 0 then 29
    else 28
    end as leap_year

    Reply
  • DECLARE @YEAR SMALLINT

    SET @YEAR=2015–2016

    SELECT @YEAR AS YEAR,

    CASE

    DAY(EOMONTH(DATEADD(YEAR,@YEAR-1900,0)+31))

    WHEN 29 THEN ‘YES’ ELSE ‘NO’

    END AS LEAP_YEAR

    GO

    Reply
  • As we are using EOMONTH, we can also use DATEFROMPARTS, because both require version 2012 or above.

    So, for 1st Feb, we can use DATEFROMPARTS (@YEAR,2,1)

    DECLARE @YEAR SMALLINT
    SET @YEAR=2000
    SELECT @YEAR AS YEAR,
    CASE
    DAY(EOMONTH( DATEFROMPARTS (@YEAR,2,1)))
    WHEN 29 THEN ‘YES’ ELSE ‘NO’
    END AS LEAP_YEAR

    Reply
  • Great ideas! Keep them coming.

    Reply
  • The Best simple way is to divide year by four if it’s remainder is zero then leap year else not a leap Year.

    declare @year int
    Declare @leapyear varchar(100)
    set @year =2015
    set @leapyear =@year%4
    select case when @leapyear=0 then cast(@year as varchar) +’ is a leap year’
    else cast(@year as varchar) + ‘ is not a leap year’ end as Leapyear

    Reply
  • Tristan Geraets
    February 12, 2015 7:25 pm

    I personally love using % for date math as it is more portable and once you get used to it also very simple ;) Here is my implementation of this solution, including source for the math.

    /*
    https://www.timeanddate.com/date/leapyear.html

    Which Years are Leap Years?
    In the Gregorian calendar 3 criteria must be taken into account to identify leap years:

    The year is evenly divisible by 4;
    If the year can be evenly divided by 100, it is NOT a leap year, unless;
    The year is also evenly divisible by 400. Then it is a leap year.
    This means that 2000 and 2400 are leap years, while 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap years.

    The year 2000 was somewhat special as it was the first instance when the third criterion was used in most parts of the world since the transition from the Julian to the Gregorian Calendar.
    */

    declare @sample table (year_num int)

    insert @sample
    (year_num)
    values (2000),
    (2001),
    (2002),
    (2003),
    (2004),
    (2005),
    (2006),
    (2007),
    (2008),
    (2009),
    (2400),
    (1900),
    (2000),
    (2100),
    (2200)

    select year_num
    ,case when (year_num % 4) = 0
    and (
    (year_num % 100) 0
    or (
    (year_num % 100) = 0
    and (year_num % 400) = 0)) then 1
    else 0
    end as is_leap_year
    from @sample

    Reply
  • Here is another method to check leap year.
    ISDATE Function is available from Version 2005 onwards, YYYY-MM-DD is ISO format. So, this method can be used in any version , any date format or any language culture.
    DECLARE @YEAR VARCHAR(10)
    SET @YEAR=’2000′
    SELECT @YEAR AS YEAR, ISDATE(@YEAR+’-02-29′)
    AS LEAP_YEAR

    Reply
  • DECLARE @YEAR AS VARCHAR(10)
    SET @YEAR=’2000′
    SELECT
    IIF(DAY(EOMONTH(TRY_CONVERT(DATE,@YEAR+’-02-02′)))=29,’YES’,’NO’) AS
    LEAP_YEAR

    Reply
  • Kiran Kumar Reddy J
    February 23, 2015 1:15 am

    HI Pinal,
    Your articles are awsome….
    Can you please tell me for SQL DBA’s how much usefull SQL Azure.What is the Feature for SQL Azure and SQL DBA.

    Reply
    • SQL Azure can help you in reduing operational costs. Microsoft site has a lot of details about SQL Azure Database.

      Reply
  • I love your solutions! Another thought I had is to do a DATEDIFF of the Feb 28th and March 1st of the year. If it’s a leap year, the answer will be 2, other wise it will be 1.

    Reply

Leave a Reply

Menu