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)

Just add one day to 28th of February that year and check for the day of month. Enjoy :)

Related Posts:

• HowTo Convert Table Values to Comma-Delimited String
• T-SQL: Generate Sequences in a Loop Query
• T-SQL: Optional Parameters in Select Query (Using ISNULL)
• MSSQL Tip: Get Primary Table in Relationship
• MSSQL Tip: Get Table Primary Key Column(s)
• ROWNUM [ PL/SQL ] Analog for T-SQL
• MSSQL Tip: Get Connected Columns in Related Tables
• How To Shrink Transaction Log in MSSQL 2000
• T-SQL: Output Parameter in sp_executesql
• MSSQL: Change tables owner to dbo with sp_changeobjectowner