How to find Leap Year using SQL Server.

DECLARE @year INT
SET @year = 2012IF (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))PRINT ’1'
ELSE
print ’0'

-----------------------------
DECLARE @Year INT = 2012SELECT ISDATE('2/29/' + CAST(@Year AS CHAR(4)))

------------------------------

A Leap year contains an extra day so there are 366 days. There are many ways to find out whether the year is a Leap year. If the value of is_leap_year is 1 it is a Leap year otherwise it is not a Leap year.
Method 1 : General method

declare @year int
set @year=2000
  
select 
 case 
  when @year%400=0  then 1 
  when @year%100=0  then 0
  when @year%4=0  then 1
  else 0
 end as is_leap_year

Method 2 : Find if the day difference between Jan 01 of this year to Jan 01 of next year is 366

declare @year int
set @year=2000
  
select 
 case 
  when datediff(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))=366 then 1
  else 0
 end as is_leap_year

Method 3 : Find if the day difference between Feb 01 to Mar 01 of this year is 29

select 
 case 
  when datediff(day,dateadd(month,1,DATEADD(year,@year-1900,0)),dateadd(month,2,DATEADD(year,@year-1900,0)))=29 then 1 
  else 0
 end as is_leap_year

Method 4 : Find if the 60th day of this year falls in February last day (Feb 29)

declare @year int
set @year=2000
  
select 
 case 
  when day(dateadd(day,59,DATEADD(year,@year-1900,0)))=29 then 1 
  else 0
 end as is_leap_year

Tags: t-sql, sql_server, sqlserver, tsql, BRH, #TSQL, #SQLServer, Leap year,

• Previous: Happy New Year from T-sql
• Next: SQL Server's equivalent of MS Access functions - Part I

 

select
 YEAR,
 IS_LEAP_YEAR =
 -- If 60th day of the year is in February, then it's leap year.
 case month(dateadd(yy,a.YEAR-1900,0)+59) when 2 then 'yes' else 'no' end
from
 (
 select YEAR = 1800 union all
 select YEAR = 1900 union all
 select YEAR = 2000 union all
 select YEAR = 2001 union all
 select YEAR = 2002 union all
 select YEAR = 2003 union all
 select YEAR = 2004 union all
 select YEAR = 2005 union all
 select YEAR = 2006 union all
 select YEAR = 2007 union all
 select YEAR = 2008 union all
 select YEAR = 2009 union all
 select YEAR = 2010 union all
 select YEAR = 2011 union all
 select YEAR = 2400
 ) a


Results:

YEAR        IS_LEAP_YEAR 
----------- ------------ 
1800        no
1900        no
2000        yes
2001        no
2002        no
2003        no
2004        yes
2005        no
2006        no
2007        no
2008        yes
2009        no
2010        no
2011        no
2400        yes

(15 row(s) affected)