CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-services
user3583912
user3583912

Reputation: 1322

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

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

Thanks..waiting for anybody reply

Upvotes: 2

Views: 5459

Answers (7)

Murdock
Murdock

Reputation: 4662

An improvement on the original answer

This has 2 improvements

  • Fixed a bug relating to subtracting -1 when a start date is after feb EVEN when the start year is not a leap year
  • Also the -1 function at the bottom should check the original start month NOT the changing 1.
CREATE FUNCTION riskstore.GetLeapYearCount (@Start Date, @End Date)
RETURNS INT
AS
BEGIN
    DECLARE @StartMonth INT = DATEPART(MONTH,@Start)
    DECLARE @StartYear INT = DATEPART(YEAR,@Start)
    DECLARE @OriginalStartYear INT = DATEPART(YEAR,@Start)
    DECLARE @EndMonth INT = DATEPART(MONTH,@End)
    DECLARE @EndYear INT = DATEPART(YEAR,@End)
    DECLARE @COUNT INT = 0
    WHILE (@StartYear <= @EndYear)
    BEGIN
        SET @COUNT = @COUNT + 
                (CASE   WHEN (@StartYear%4 = 0 AND @StartYear%100 !=0) OR @StartYear%400 = 0 
                        THEN 1 
                        ELSE 0 END)
        SET @StartYear = @StartYear + 1
    END
    SET @COUNT = @COUNT + CASE WHEN @StartMonth >= 3 AND ((@OriginalStartYear%4 = 0 AND @OriginalStartYear%100 !=0) OR @OriginalStartYear%400 = 0) THEN -1 ELSE 0 END
    RETURN (@COUNT)
END

Upvotes: 1

KISS
KISS

Reputation: 81

Number of leap days between two dates.

DECLARE
    @StartDate DATETIME = '2000-02-28',
    @EndDate DATETIME = '2017-02-28'

SELECT ((CONVERT(INT,@EndDate-58)) / 1461 - (CONVERT(INT,@StartDate-58)) / 1461)

-58 to start counting from 1st March 1900 and / 1461 being the number of days between 29th Februaries. NOTE: in Excel, the -58 would be -60 as 1st Jan 1900 in Excel is day 1 but in SQL is day zero and SQL doesn't recognise 29th Feb 1900 whereas Excel does. ALSO NOTE: This formula will go wrong every 400 years as every 400 years we skip a leap year. Hope this helps someone.

Upvotes: 2

anyolejoe
anyolejoe

Reputation: 11

Here's a sql function based on some of the answers above that should do this -

CREATE FUNCTION [dbo].[LeapDayCount]
(
    @StartDate as datetime,
    @EndDate as datetime
)
RETURNS int as
BEGIN

    DECLARE @StartYear int
    DECLARE @EndYear int
    DECLARE @Year int

    SELECT @StartYear = YEAR(@StartDate)
    SELECT @EndYear = YEAR(@EndDate)

    DECLARE @Count int

    SET @Count = 0

    SET @Year = @StartYear

    WHILE (@Year <= @EndYear)
    BEGIN
        SET @Count = @Count + 
                    (CASE WHEN (@Year%4 = 0 AND @Year%100 !=0) OR @Year%400 = 0 
                            THEN 1 ELSE 0 END)

        SET @Year = @Year + 1
    END

    --remove one leap day if start date is a leap year but after february
    IF ((@StartYear%4 = 0 AND @StartYear%100 !=0) OR @StartYear%400 = 0) AND MONTH(@StartDate) > 2 SET @Count = @Count -1

        --remove one leap day if end date is a leap year but less than 29th Feb
    IF ((@EndYear%4 = 0 AND @EndYear%100 !=0) OR @EndYear%400 = 0) AND (MONTH(@EndDate) = 1 OR (MONTH(@EndDate) = 2 AND DAY(@EndDate) < 29)) SET @Count = @Count -1

RETURN @Count
END

Upvotes: 1

Jithin Shaji
Jithin Shaji

Reputation: 6073

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

Upvotes: 1

Jithin Shaji
Jithin Shaji

Reputation: 6073

Hope this too works. 

DECLARE @X INT = 1590
DECLARE @Y INT = 1603

DECLARE @COUNT INT = 0,@Z INT = @X

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

SELECT @Z BEGIN_YEAR,@Y END_YEAR,@COUNT NO_OF_LEAP_YEARS

Result

enter image description here

Upvotes: 1

valex
valex

Reputation: 24144

It's not clear what you want to do with the first and last year depend on your date parameters. Here is an example how to do it with the recursive query:

with cte as
(
     select YEAR('1900-01-01') as [year]
     union all
     select [year] + 1
     from    cte   
     where   [year] + 1 <= YEAR('2100-01-01')
 )
 SELECT COUNT(*)
 FROM cte WHERE
           ([YEAR]%4=0) AND (([YEAR]%100<>0) OR ([YEAR]%400=0))
 OPTION(MAXRECURSION 1000)

SQLFiddle demo

Upvotes: 1

mohan111
mohan111

Reputation: 8865

DECLARE @year int
SET @year = 2008
if (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))
print 'Leap year'
ELSE
print 'No'

Upvotes: 1

Related Questions