| displaying dates between two dates [message #11011] |
Mon, 01 March 2004 01:51  |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
What is the best way to show/display all dates between two dates like between 01-feb-2004 and 29-feb-2004.
I need to use the above in a query.
output should be
01-feb-04
02-feb-04
03-feb-04
.
.
.
.
27-feb-04
28-feb-04
Thanks in advance
|
|
|
|
| Re: displaying dates between two dates [message #11017 is a reply to message #11011] |
Mon, 01 March 2004 03:38  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you search these forums, you will find several examples similar to the following. You can also use substitution variables or host variables to prompt for input of different date parameters. You can either obtain your rownums from a table such as all_objects with sufficient rows or a union all of dual or other tables or use cube with sufficient numbers, as I have below. It all depends on what you want. If you are going to use various dates, then a combination of bind variables and cube is probably the most efficient.
scott@ORA92> SELECT TO_DATE ('01-feb-2004', 'dd-mon-yyyy') + ROWNUM - 1
2 FROM (SELECT 1 FROM DUAL GROUP BY CUBE (1, 2, 3, 4, 5))
3 WHERE TO_DATE ('01-feb-2004', 'dd-mon-yyyy') + ROWNUM - 1 <=
4 TO_DATE ('28-feb-2004', 'dd-mon-yyyy');
TO_DATE('
---------
01-FEB-04
02-FEB-04
03-FEB-04
04-FEB-04
05-FEB-04
06-FEB-04
07-FEB-04
08-FEB-04
09-FEB-04
10-FEB-04
11-FEB-04
12-FEB-04
13-FEB-04
14-FEB-04
15-FEB-04
16-FEB-04
17-FEB-04
18-FEB-04
19-FEB-04
20-FEB-04
21-FEB-04
22-FEB-04
23-FEB-04
24-FEB-04
25-FEB-04
26-FEB-04
27-FEB-04
28-FEB-04
28 rows selected.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
