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