user3583912 user3583912 - 3 months ago 24
SQL Question

how to calculate number of leap years between two dates in t-sql?

I am doing report in SSRS, i need data set column for calculating number of leap years between two dates in t-sql. I found function for single input parameter weather it is leap year or not but for my requirement two parameters in function or any t-sql statement.

Thanks..waiting for anybody reply

Answer

I thought, will add as another answer.

DECLARE @A DATE = '2008-03-23',
    @B DATE = '2012-04-20'

DECLARE @AM INT,@AY INT,@BM INT,@BY INT
SET @AM = DATEPART(MONTH,@A),   --3
    @AY = DATEPART(YEAR,@A),    --2008
    @BM = DATEPART(MONTH,@B),   --4
    @BY = DATEPART(YEAR,@B)     --2012

DECLARE @COUNT INT = 0

WHILE (@AY <= @BY)
BEGIN
    SET @COUNT = @COUNT + 
                (CASE   WHEN (@AY%4 = 0 AND @AY%100 !=0) OR @AY%400 = 0 
                        THEN 1 
                        ELSE 0 END)
    SET @AY = @AY + 1
END

SET @COUNT = @COUNT + CASE WHEN @AM >= 3 THEN -1 ELSE 0 END

SELECT @A BEGIN_DATE,@Y END_DATE,@COUNT NO_OF_LEAP_YEARS

As I dont have an instance of sql server available now,I did not test the code..But you will get the an idea about what I was trying to achieve. I declared @BM, in case you want to do the checking with the end month too..