Wednesday, March 12, 2008

T-SQL: Leap Year Check

There are various techniques to determine whether a given year is a leap year. The most common way which is widely taught at schools and universities is to detect whether the given year is divided by 4, 100 and not by 400, something like that.


Below is a small T-SQL function that checks for leap year while it uses more smart - shifting technique, which I advice to use where possible.


ALTER FUNCTION F_BIT_LEAP_YEAR
(@p_year SMALLINT)
RETURNS BIT
AS
BEGIN
DECLARE @p_leap_date SMALLDATETIME
DECLARE @p_check_day TINYINT

SET @p_leap_date = CONVERT(VARCHAR(4), @p_year) + '0228'
SET @p_check_day = DATEPART(d, DATEADD(d, 1, @p_leap_date))
IF (@p_check_day = 29)
RETURN 1

RETURN 0
END

Use this way:


SELECT dbo.F_BIT_LEAP_YEAR(2003)

No comments: