How to Check whether a Year is Leap Year or not in SQL Server

T-SQL Query or Scalar User Defined Function to Check a whether Year is Leap Year or not in SQL Server

USE TAMATAM

GO

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

Method-I : SQL Query

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

DECLARE @Year as INT , @Days_in_Feb as INT

SET @Year=2018 --Pass your Year That you want to Check

SET @Days_in_Feb=DAY(EOMONTH(CONCAT(@Year,'0201')))

--SET @Days_in_Feb =DATEPART(DD,(EOMONTH(CONCAT(@year,'0201'))))

IF @Days_in_Feb=29

  BEGIN

       Print 'Its a Leap Year'

  END

ELSE

 BEGIN

       Print 'Its a Non-Leap Year'

 END

 GO

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

Method-II : Scalar User Defined Function

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

DROP FUNCTION IsLeapYear;

GO

--Creating the Function to Check whether the Year is a Leap Year or Not

--Function returns an Integer output as True(1) or False(0)

CREATE FUNCTION dbo.IsLeapYear (@year INT)

RETURNS INT

AS

BEGIN

             RETURN( IIF( DAY(EOMONTH(CONCAT(@Year,'0201'))) = 29,1,0 ))

END

GO

--Executing the Function

SELECT dbo.IsLeapYear('2016') 'IsLeapYear';

SELECT dbo.IsLeapYear('2018') 'IsLeapYear';

GO

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

Method-III : Scalar User Defined Function

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

DROP FUNCTION IsLeapYear ;

GO

--Creating the Function to Check whether the Year is a Leap Year or Not

--Function returns a Varchar output as True('Leap Year') or False('Non Leap Year')

CREATE FUNCTION dbo.IsLeapYear (@Year INT)

RETURNS VARCHAR(50)

AS

BEGIN

     RETURN(IIF(DAY(EOMONTH(CONCAT(@Year,'0201'))) = 29,'Leap Year',
'Non Lear Year'))

END

GO

--Executing the Function

SELECT dbo.IsLeapYear('2016') 'IsLeapYear';

SELECT dbo.IsLeapYear('2018') 'IsLeapYear';

GO

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

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