Increment Dates 1

[MikeAuz1979]

Hi Using Access 2000, I'm trying to get a query to return every date between a start and end date(supplied by a another query)

So QryDateRange looks like
Start End
01-jan-01 05-jan-01

and I want my query to output.
OutputDates
01-jan-01
02-jan-01
03-jan-01
04-jan-01
05-jan-01

I'm currently acheiving this by having a table (tblAllDates) with the field 'Dates' containing all the dates from 01-jan-01 to 31-dec-10 and then having a query that is performing a cartesian join on tblAllDates and QryDateRange and restricting tblAllDates to the required date range.
(dhookom in thread701-1082717 also states this as a solution to this problem.)

Thanks for any help
Mike

 

[r937]

here's another approach, using an integers table

thread232-1206446

it's in the ColdFusion forum, but the database being used is Access

let me know if you need help marrying that concept with your date range

r937.com | rudy.ca

 

[MikeAuz1979]

Hi, r937

Big thanks, great referencing, for anyone following the thread the code for creating sequential dates is:

SELECT DateAdd("d",100*[h].+10*[t].+.,#10/1/2006#) AS ddate
FROM integers AS h, integers AS t, integers AS u
WHERE (((100*[h].+10*[t].+.) Between 0 And 364))
ORDER BY 100*h.i+10*t.i+u.i;

Where the access table integers has 1 field called i and contains 0-9, #01/01/2006# is the starting date and 364 is the number of dates you want.

But if anyone can help further I'd still like to create these sequential dates purely in a query, the reason for this I have a sql Server Pass through query that is doing some manipulation and then I'm applying this 'creations of date intelligence' to the result in a seperate query. If I had some magic piece of sql that created the sequential dates I could include it in my pass through query and negate the need for the 2nd query.

Hope that makes sense and thanks again for any help
Mike

 

[r937]

can you not create the integers table in sql server?

r937.com | rudy.ca

 

[MikeAuz1979]

Well I don't have access to create tables in the SQL Server, but I am checking with IT to see if it will be too much of an elongated process to get added.

Even though, it would be so tidy to get a result that didn't require a table at all. (If somebody has a magical solution.)

Thanks again for your help

Mike

 

[r937]

magic solution? inline tables

select dateadd("d",100*h.i+10*t.i+u.i,#10/1/2006#) as ddate
from (
     select 0 as i
     union all select 1
     union all select 2
     union all select 3
     union all select 4
     union all select 5
     union all select 6
     union all select 7
     union all select 8
     union all select 9
     ) as h
   , (
     select 0 as i
     union all select 1
     union all select 2
     union all select 3
     union all select 4
     union all select 5
     union all select 6
     union all select 7
     union all select 8
     union all select 9
     ) as t
   , (
     select 0 as i
     union all select 1
     union all select 2
     union all select 3
     union all select 4
     union all select 5
     union all select 6
     union all select 7
     union all select 8
     union all select 9
     ) as u
 where 100*h.i+10*t.i+u.i between 0 and 364
order 
    by 100*h.i+10*t.i+u.i

r937.com | rudy.ca

 

[r937]

oh, wait, for a passthrough query to SQL Server, the first line should be

select dateadd(d,100*h.i+10*t.i+u.i,'2006-10-01') as ddate


r937.com | rudy.ca

 

[MikeAuz1979]

Thanks again r937 but I can seem to get ths query to work, I tried in a Access select query, a union query and a pass through query (To the sql server) but each time I get an error saying 'syntax error at from clause'

Mike

 

[r937]

well, i can assure you that it works fine in SQL Server (assuming you change the first line as shown)

and of course it will never work in Access, because in Access each SELECT must have a FROM clause (i.e. SELECT 9 by itself is fine in SQL Server, invalid in Access)

r937.com | rudy.ca

 

[MikeAuz1979]

Sorry r937 your right it work's fine with the corrected first line. (I have a suspicion that tek-tips was slow on updating that post and that it wasn't there when I first tried it out.)

Thanks again, great result.