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
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..