How To Find Leap Year In SQL Server?

There is no straightforward way to find leap year in SQL Server. We have to use our own logic to find whether the given year is leap or not. Here I have gone through couple of methods to find the the leap year.

1. February Extra Day Method

In this method we will be checking whether the February month of the year has 29 days. If yes then we can say it’s a leap year. Here is the script:

DECLARE @GivenYear varchar(4)
SET @GivenYear = '1904'
SELECT
  CASE DATEPART(month, 
           DATEADD(day, 1, 
               CAST((@GivenYear + '-02-28') 
       as datetime)))
    WHEN 2 
      THEN 1
    ELSE 0
  END
GO

2. Leap Year Rule Method

In this method we will apply the standard leap year rule and create a logic to identify the leap year. The rules or algorithm of leap year is:

• 4th year test: If year is divisible by 4.
• 100th year test: And, if year is not divisible by 100.
• 400th year test: Or, if year is divisible by 400.

DECLARE @GivenYear int
SET @GivenYear = 1900
IF ((@GivenYear % 4 = 0 AND @GivenYear % 100 <> 0) 
		OR @GivenYear % 400 = 0)
  SELECT 1
ELSE
  SELECT 0
GO

Reference

• About leap year algorithm at Wikipedia.
• More SQL Server Tips.