Dates in a leap year for a given date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dates in a leap year for a given date range

Author

Topic

Nikkap
Starting Member

8 Posts

Posted - 2007-03-12 : 09:17:45

I have a table "Periods"
StartPeriod EndPeriod
----------- ----------
10/03/2003 30/11/2004
01/12/2004 15/12/2004
16/12/2004 31/12/2006

desired output

StartPeriod LastPeriod DatesInLeapYear
----------- --------- ------------
10/03/2003 30/11/2004 334
01/12/2004 15/12/2004 13
16/12/2004 31/12/2005 15

I would like the day difference only for those days which are in a leap year

Thank u very much

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-12 : 23:34:56

is it some kind of school exercise?

Post your table structure.
I think you need a stored procedure to do this.
mk_garg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 00:14:40

[code]
-- Create the function
create function fn_DaysInLeapyear
(
@start_date datetime,
@end_date datetime
)
returns int
as
begin
declare @yr int,
@days int

select @yr = year(@start_date),
@days = 0

while @yr <= year(@end_date)
begin
if (@yr % 100 = 0 and @yr % 400 = 0) -- Leap Year
or (@yr % 100 <> 0 and @yr % 4 = 0)
begin
select @days = @days +
case when @yr = year(@start_date) and @yr = year(@end_date)
then datediff(day, @start_date, @end_date)
when @yr = year(@start_date)
then datediff(day, @start_date, dateadd(year, datediff(year, 0, @start_date) + 1, -1))
when @yr = year(@end_date)
then datediff(day, dateadd(year, datediff(year, 0, @end_date), 0), @end_date)
else datepart(dayofyear, convert(varchar(4), @yr) + '1231')
end
end
select @yr = @yr + 1
end

return @days
end
go
-- Create the table variable
declare @Periods table
(
StartPeriod datetime,
EndPeriod datetime
)
-- insert sample data
insert into @Periods
select '20030310', '20041130' union all
select '20041201', '20041215' union all
select '20041216', '20061231'

-- query
select *,
DatesInLeapYear = dbo.fn_DaysInLeapyear(StartPeriod, EndPeriod)
from @Periods
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-13 : 01:49:07

khtan, the year of 2100 is NOT a leap year, even if modula 4 is zero...

IF Year % 400 = 0 THEN LeapYear = True
ELSEIF Year % 100 = 0 THEN LeapYear = False
ELSEIF Year % 4 = 0 THEN LeapYear = True
END IF

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 02:15:02

quote:
Originally posted by Peso

khtan, the year of 2100 is NOT a leap year, even if modula 4 is zero...

IF Year % 400 = 0 THEN LeapYear = True
ELSEIF Year % 100 = 0 THEN LeapYear = False
ELSEIF Year % 4 = 0 THEN LeapYear = True
END IF

Peter Larsson
Helsingborg, Sweden

Oh yeah. forgotten about that.

. 2000 seems so far away now

Nikkap
Starting Member

8 Posts

Posted - 2007-03-13 : 03:42:07

Thank u so much khtan
This is all I wanted .
Thank u again !!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources