Monday, February 20, 2017

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 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog