User Defined Function (UDF) to Determine a Leap Year in SQL Server

A leap year is a year containing one additional day and lasts 366 days instead of the usual 365.
Rule to determine if a year is a leap year:
- leap year is any year that is divisible by 400 (2000, 2400 are leap years...) or by 4 (2004, 2008 are leap years...) but it must not be divisible by 100 (2100, 2200 are NOT leap years...)

We will create a User Defined Function (UDF) that accepts a DATETIME value and checks if a year is a leap year:

CREATE FUNCTION [dbo].[ufnLeapYearCheck] (@MyDate DATETIME)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnString varchar(50)
SET @ReturnString = CASE WHEN (YEAR(@MyDate) % 4 = 0 AND YEAR(@MyDate) % 100 != 0)
OR YEAR(@MyDate) % 400 = 0
THEN 'The year is a leap year'
ELSE 'The year is NOT a leap year'
END
RETURN @ReturnString
END
GO

To use this user-defined function:

SELECT dbo.ufnLeapYearCheck ('2004-05-27')

leap_year_check_udf_example1

SELECT dbo.ufnLeapYearCheck ('2000-09-15')

leap_year_check_udf_example2

You can create a User Defined Function (UDF) that accepts an INT value and checks if a year is a leap year:

CREATE FUNCTION [dbo].[ufnLeapYearCheck2] (@MyYear INT)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnString varchar(50)
SET @ReturnString = CASE WHEN (@MyYear % 4 = 0 AND @MyYear % 100 != 0)
OR @MyYear % 400 = 0
THEN 'The year is a leap year'
ELSE 'The year is NOT a leap year'
END
RETURN @ReturnString
END
GO

To use this user-defined function:

SELECT dbo.ufnLeapYearCheck2 (2001)

leap_year_check2_udf_example1

SELECT dbo.ufnLeapYearCheck2 (2100)

leap_year_check2_udf_example2

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">