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.
Testing Your Leap Year Logic
The calendar rule has one twist that trips up many hand written checks. A year divisible by 4 gets February 29, except century years, which get it only when they are divisible by 400. So 2000 had February 29, but 1900 did not, and 2100 will not.
The nice thing about the EOMONTH approach is that SQL Server already knows this rule, so you do not have to code it yourself. If you want the arithmetic version anyway, it looks like this: (@year % 4 = 0 AND @year % 100 <> 0) OR @year % 400 = 0. Another simple method is to count the days in the year: DATEPART(DAYOFYEAR, DATEFROMPARTS(@year, 12, 31)) returns 366 only when February has 29 days.
You can also apply the same check to a whole column at once, with no loop. For example, SELECT YearNo FROM dbo.Years WHERE DAY(EOMONTH(DATEFROMPARTS(YearNo, 2, 1))) = 29 returns only the years that have February 29.
Whichever method you pick, test it with a few known years before you use it. 1900, 2000, 2023 and 2024 are enough to cover every branch of the rule. It takes a minute and saves you from a bug that would only show up once every few years. Also remember that EOMONTH and DATEFROMPARTS need SQL Server 2012 or later.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.