Number of leap days in date range 1

[marcusmco]

Hello,

I am having difficulties sorting out the best way to find the number of leap days in a date range. For example, if the date range is 01 Jan 2008 - 15 April 2008 it would be 1. If it is 01 Jan 2003 - 15 April 2008, it would be 2. Any ideas?

I am using T-SQL.

Thanks,
Marcus

 

[MichaelRed]

Count where Month = 'Feburary' and Day = '29'

?

MichaelRed

 

[PWise]

Try

Declare @StartDate datetime
Declare @EndDate datetime
Declare @NumDays int

Select  @StartDate = '1/1/1', @EndDate= '12/31/10'
Select @NumDays=Datediff(d,@StartDate,@EndDate)
CREATE TABLE [#Digits] (
    [DigitID] [int] IDENTITY NOT NULL ,
    CONSTRAINT [PK_Digits] PRIMARY KEY  CLUSTERED 
    (
        [DigitID]
    )  ON [PRIMARY] 
) ON [PRIMARY]

DECLARE @position int
SET @position = 1
SET IDENTITY_INSERT [#Digits]  ON 
WHILE @position <= @numdays
BEGIN
   insert into #Digits(DigitID) values (@position)
   SET @position = @position + 1
END
SET IDENTITY_INSERT [#Digits]   OFF 
Select count(*) from(
Select Dateadd(d,DigitID-1,@StartDate) D
from #Digits)dt 
where month(d)=2 and day(d)=29
drop table #Digits

 

[marcusmco]

Thanks to both of you. I managed to create this that appears to work:



/* Top 4 rows will need to be input params in function */
DECLARE @StartDate AS datetime /* Start date in range to check */
DECLARE @EndDate AS datetime /* End date in range to check */
SET @StartDate = '2004-02-29'
SET @EndDate = '2008-02-29'

DECLARE @LoopDate AS datetime
DECLARE @LeapDays as int
DECLARE @LoopCounter as int
DECLARE @Months as int

SET @LoopDate = @StartDate
SET @Months = DateDiff(m, @StartDate, @EndDate) + 1
SET @LeapDays = 0

/* Loop all months to check if any Feb has 29 days. If so, increment @LeapDays. */
SET @LoopCounter = 0
WHILE @LoopCounter < @Months
BEGIN
SET @LoopDate = DATEADD(m, @LoopCounter, @StartDate)

/* check if month = february and day = 29 */
IF (MONTH(@LoopDate) = 2 AND DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@LoopDate)+1,0))) = 29)
BEGIN
SET @LeapDays = @LeapDays + 1
END

SET @LoopCounter = @LoopCounter + 1
END

/* if the day of the last month in the date range is not a leap day, then subtract 1 from @LeapDays */
IF NOT (MONTH(@EndDate) = 2 AND DAY(@EndDate) = 29)
BEGIN
SET @LeapDays = @LeapDays - 1
END

PRINT Cast(@LeapDays as nvarchar) + ' Leap days' /* For testing purposes */

 

[bborissov]

It is always good to have a number table in your DB, but in case you didn't:

[COLOR=blue]DECLARE[/color] @dBegin [COLOR=#FF00FF]datetime[/color], @dEnd [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]SET[/color] @dBegin = [COLOR=red]'20040229'[/color]
[COLOR=blue]SET[/color] @dEnd   = [COLOR=red]'20080229'[/color]

[COLOR=green]-- Single select statement here
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]COUNT[/color](*) [COLOR=blue]AS[/color] LeapDays
[COLOR=blue]FROM[/color] (
[COLOR=blue]select[/color] @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
[COLOR=blue]from[/color]       ([COLOR=blue]select[/color] 0 b1  union [COLOR=blue]select[/color] 1    b1)  t1
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b2  union [COLOR=blue]select[/color] 2    b2)  t2
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b3  union [COLOR=blue]select[/color] 4    b3)  t3
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b4  union [COLOR=blue]select[/color] 8    b4)  t4
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b5  union [COLOR=blue]select[/color] 16   b5)  t5
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b6  union [COLOR=blue]select[/color] 32   b6)  t6
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b7  union [COLOR=blue]select[/color] 64   b7)  t7
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b8  union [COLOR=blue]select[/color] 128  b8)  t8
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b9  union [COLOR=blue]select[/color] 256  b9)  t9
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b10 union [COLOR=blue]select[/color] 512  b10) t10
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b11 union [COLOR=blue]select[/color] 1024 b11) t11
[COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b12 union [COLOR=blue]select[/color] 2048 b12) t12
[COLOR=blue]where[/color] @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
[COLOR=blue]WHERE[/color] [COLOR=#FF00FF]DAY[/color](range_date) = 29 AND [COLOR=#FF00FF]MONTH[/color](range_date) = 2

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.