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)
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
DECLARE @DATE DATETIME
SET @DATE=’2012-Feb-29′
SELECT CASE (year(@date)%4)
when 0 then 29
else 28
end as leap_year
Traditional way above logic, but you are logic awsome
Thanks
Karthik
That is not correct since 1900 was NOT a leap year (neither will be 2100 (but 2000 was))…
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
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
Great ideas! Keep them coming.
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
You are missing a conditional to determine the leapyear, see https://www.timeanddate.com/date/leapyear.html
Thanks for the info i was not aware of other criteria other then divisible by four.
We all are learning here. Good stuff Tristan – thanks for adding valuable comments.
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
Thanks @Tristan
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
Good one Harsh.
DECLARE @YEAR AS VARCHAR(10)
SET @YEAR=’2000′
SELECT
IIF(DAY(EOMONTH(TRY_CONVERT(DATE,@YEAR+’-02-02′)))=29,’YES’,’NO’) AS
LEAP_YEAR
Good Trick.
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.
SQL Azure can help you in reduing operational costs. Microsoft site has a lot of details about SQL Azure Database.
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.
Damn! I loved your idea.