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.