Select all dates between two dates

How can I select all of the dates between two dates?

For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:

12/2/2003
12/3/2003
12/4/2003
12/5/2003

Is there a built in function for this? Is there a way for a function to return multiple rows?

It has to be a function because I need to use it within other SQL statements.

Assuming you have a "DATE" Oracle type column in a table. Let's presuem the column is named "foo". Then you want, in your WHERE cluase:

TO_DATE('12/2/2003','MM/DD/YYYY') <= foo
AND
TO_DATE('12/5/2003','MM/DD/YYYY') >= foo

you may obviously change the literal date strings which are the first args to be whate3ver you're searchign for.

Note that I"m assumin you populated the 'foo' column with statements like:

SET (foo) VALUES ( TO_DATE('12/2/2003','MM/DD/YYYY') )

The TO_DATE() above in the WHERE will be midnight on that date... if your 'foo' col has time information (non-midnight date stamp) your where might get a little funny...

-c

Functions can be return only one value in any programming language. Why dont you use the GROUP BY clause in SQL statement. Can you please send your query that you want to write?. An example of GROUP BY is look like that:

SELECT hiredate from emp
WHERE hiredate between '01-JAN-2003' AND '01-OCT-2003'
GROUP BY hiredate
/

Regards,

GROUP BY won't work for this project because it won't return any dates for which there are no entries in the table. Lets say we have a (simplified) table of orders like this:

order_id INTEGER
order_date DATE
order_total NUMBER(10,2)

If we have these entries:

1 12/2/2003 12.30
2 12/2/2003 45.60
3 12/4/2003 78.90
4 12/5/2003 10.23

I need to select all the dates and how much was sold on each one, even 12/3 when nothing was sold:

date num amount
12/2/2003 2 57.90
12/3/2003 0 00.00
12/4/2003 1 78.90
12/5/2003 1 10.23

So, how do I fill in the extra dates with no entries? My initial thought was to somehow select each day between the two endpoints, outer join them to the orders, then group by to get the totals...

It has to be SQL, I can't use a procedure in this context.

Hi augustd,

You asked: So, how do I fill in the extra dates with no entries?

One way to do it is via an inline-view, which will hold all the dates in the range.
For example, the following query produces all the dates in the month of August (-2: two months ago).
Then, you can use an outer join with this inline-view to ensure that each day is in the result set.


select to_char(sysdate + 1 - rownum,'DD-Mon') as d
from all_objects
where trunc(sysdate + 1 - rownum,'MM') >= trunc(add_months(sysdate,-2),'MM')
minus
select to_char(sysdate + 1 - rownum,'DD-Mon') as d
from all_objects
where trunc(sysdate + 1 - rownum,'MM') > trunc(add_months(sysdate,-2),'MM')

Cheers,
Dan

We have a winner! Thanks Dan.

I modified it a little bit to get my arbitrary date range:

SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum AS d
FROM all_objects
WHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/05/2003', 'MM/DD/YYYY')

Returns:

D
2003-12-01 00:00:00
2003-12-02 00:00:00
2003-12-03 00:00:00
2003-12-04 00:00:00
2003-12-05 00:00:00

Hi can tell me what rownum implies in ur query???

In Oracle rownum is a pseudo column and its value not saved in the database, it shows the number of each record in the table, for example if you have five records in your table called 'department' and you run the query using rownum defined in your SELECT clause then your output would be look like that:


SELECT rownum, deptno, dname
FROM department
/

rownum deptno dname
------- ------- -------------------
1 10 ACCOUNTS
2 20 SALES
3 30 MARKETING
4 40 PURCHASE
5 50 I.T

Right, so date + rownum has the effect of adding one day to each date returned.

So to return the next ten days:

select rownum, trunc(sysdate + rownum) AS day
from all_objects
where rownum <= 10

ROWNUM DAY
1 2004-08-21 00:00:00
2 2004-08-22 00:00:00
3 2004-08-23 00:00:00
4 2004-08-24 00:00:00
5 2004-08-25 00:00:00
6 2004-08-26 00:00:00
7 2004-08-27 00:00:00
8 2004-08-28 00:00:00
9 2004-08-29 00:00:00
10 2004-08-30 00:00:00

dear sir
i have a button on a form.
behind that button i have
one select statement and othe insert into statement after one an other

if i run only insert statement it works
but
if i run select statement first and after that an insert statement ,
it gives error
cannot insert.
please help me for that problem

raza

I'm not sure what you are asking. Are you getting an error message? What is your SQL? Does this have to do with dates?

By mistaken someone posted his question here, it is a separate thread, this question does not concern with date.

FYI - Dan's solution will work great. Not that you should every run into this but just so your informed. You will only be able to return the number of records that exist in the table in your from clause. Also keep in mind that the table you use can effect performance.