Nested WHILE loop - SQL Server 2000

Question

In SQL Server 2000, I want to be able to list all dates between 2 dates including hours for each day so if I have @fromdate = 2016-07-01 00:00:00 and @todate = 2016-07-05 00:00:00 then I need a list of:

2016-07-01 00:00:00
2016-07-01 01:00:00
2016-07-01 02:00:00
2016-07-01 03:00:00
2016-07-01 04:00:00
...
2016-07-04 23:00:00
2016-07-05 00:00:00

Now this query works half way through:

declare @fromdate as datetime
declare @todate as datetime

set @FromDate = '2016-07-11 00:00:00.000'
set @ToDate = '2016-07-21 00:00:00.000'

declare @i int, @j int

set @i = convert(int, @todate - @fromdate)
set @j = 0

while @i >= 0
begin
  while @j < 24
  begin
     select dateadd(hour, @j, @ToDate - @i) as date, @j as Hour
     set @j = @j + 1
  end

  set @i = @i - 1
end

It returns an hourly split for the first day but doesn't jump to the next day. If I disable the inner while loop the outer loop works fine without hours. Any suggestions are welcome, remember it's a SQL Server 2000 so nothing advanced will work.

Possible duplicate of [How to generate a range of dates in SQL Server](http://stackoverflow.com/questions/11141507/how-to-generate-a-range-of-dates-in-sql-server) Just change `day` for `hour` — Juan Carlos Oropeza, Jul 12 '16 at 18:21
Especially since this is sql 2000 you need a numbers/tally table. BTW, are you ever going to upgrade? It has been out of support for over 6 years now. Just because we still have a 14.4 modem doesn't mean we should keep using it. :) — Sean Lange, Jul 12 '16 at 18:32
Sql Server 2000 is way past end of life. It no longer gets any patches, even for critical security issues, and hasn't for some time now. Continuing to use it is highly irresponsible. Upgrading this server is job 1. — Joel Coehoorn, Jul 23 '16 at 18:56

Tab Alleman · Answer 1 · 2016-07-12T18:46:28.840

1

Treating your question as academic, this is why your code isn't working:

You need to reset @j to zero after the inner loop completes. Otherwise @j stays at "24" and the inner loop never executes again.

As others have pointed out, there are other, possibly better, ways to do what you are trying to do.

edited Jul 12 '16 at 18:46

answered Jul 12 '16 at 18:09

Tab Alleman

31,483
7
36
52

Juan Carlos Oropeza · Answer 2 · 2016-07-12T18:27:43.893

I think you can do something simple. I didnt check the dateadd sintaxis, but just add 1 hour each time until you reach target date

declare @fromdate as datetime
declare @todate as datetime
declare @printDate as datetime

set @FromDate = '2016-07-11 00:00:00.000'
set @ToDate = '2016-07-21 00:00:00.000'
set @printDate = @FromDate

while @printDate <= @todate
begin
     select @printDate as Hour
     set @printDate = dateadd(hour, 1, @printDate )
end

score 0 · Answer 3 · answered Jul 12 '16 at 18:14

This will give you your dates by hour that you need, in order, based on your range, in a table.

create table #dates(
dates datetime)

declare @fromdate as datetime
declare @todate as datetime

set @FromDate = '2016-07-11 00:00:00.000'
set @ToDate = '2016-07-21 00:00:00.000'

declare @ct int 
set @ct = datediff(hour,@FromDate,@ToDate)

while @ct > 0
  begin
    insert into #dates (dates)
    select dateadd(hour,@ct,@FromDate)
    set @ct = @ct - 1
  end

select * from #dates

--RESULTS
2016-07-21 00:00:00.000
2016-07-20 23:00:00.000
2016-07-20 22:00:00.000
2016-07-20 21:00:00.000
2016-07-20 20:00:00.000
2016-07-20 19:00:00.000
2016-07-20 18:00:00.000
2016-07-20 17:00:00.000
2016-07-20 16:00:00.000
....etc...

score 0 · Answer 4 · answered Jul 12 '16 at 18:15

To add, you may also want to use a table variable to speed things up and to have one result set:

declare @table table (DateHour datetime, Hour int)
declare @fromdate as datetime
declare @todate as datetime

set @FromDate = '2016-07-11 00:00:00.000'
set @ToDate = '2016-07-21 00:00:00.000'

declare @i int, @j int

set @i = convert(int, @todate - @fromdate)
set @j = 0
while @i >= 0
begin
  while @j <24
  begin
     insert @table
     values (dateadd(hour, @j, @ToDate - @i), @j) --as Hour
     set @j=@j+1
  end
set @j=0
set @i=@i-1
end
select * from @table

score 0 · Answer 5 · answered Jul 12 '16 at 18:56

To demonstrate the tally table concept I put this together. This will work just fine in sql 2000. First step is creating the persistent tally table. This will only need to be done the very first time you use this kind of table. In later versions of sql I would create this differently but it still works. If you want you could even add a trigger to disallow inserts or deletes so you can ensure you have no gaps.

create Table MyTally
(
    N int identity(1,1) 
)
GO

insert MyTally
DEFAULT VALUES
go 10000 --This will cause the previous batch to loop that many times.

Now that we have a table with 10,000 incrementing values we can use it.

declare @fromdate as datetime
declare @todate as datetime

set @FromDate = '2016-07-11 00:00:00.000'
set @ToDate = '2016-07-21 00:00:00.000'

select dateadd(hour, x.N - 1, DATEADD(day, t.N - 1, @FromDate))
from cteTally t
cross join 
(
    select N
    from cteTally
    where N <= 24
)x
where t.N <= DATEDIFF(day, @FromDate, @todate) + 1

This creates the 264 rows you would need for the 11 days between those two dates.

Nested WHILE loop - SQL Server 2000

5 Answers5