Home » SQL & PL/SQL » SQL & PL/SQL » How to get all dates between two dates in Oracle?
How to get all dates between two dates in Oracle? [message #9762] Wed, 03 December 2003 14:53 Go to next message
Mahendra
Messages: 23
Registered: January 2002
Junior Member
Hi,
Can anyone help me in getting all dates between two dates in Oracle sql or PL/SQL.
Thanx in advance,
Mahendra
Re: How to get all dates between two dates in Oracle? [message #9763 is a reply to message #9762] Wed, 03 December 2003 16:01 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
SQL> SET VERIFY OFF
SQL> SELECT TO_CHAR(TO_DATE('&&date_1','DD-MON-YYYY') + ROWNUM - 1,'DD-MON-YYYY') dt
  2  FROM   sys.all_objects
  3  WHERE  ROWNUM <= TO_DATE('&&date_2','DD-MON-YYYY')
  4                   -
  5                   TO_DATE('&&date_1','DD-MON-YYYY')
  6                   +
  7                   1
  8  /
Enter value for date_1: 25-FEB-2004
Enter value for date_2: 03-MAR-2004
  
DT
-----------
25-FEB-2004
26-FEB-2004
27-FEB-2004
28-FEB-2004
29-FEB-2004
01-MAR-2004
02-MAR-2004
03-MAR-2004
  
8 rows selected.
  
SQL>
HTH,

A.
Re: How to get all dates between two dates in Oracle? [message #9770 is a reply to message #9763] Thu, 04 December 2003 00:26 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
A little explication with the solution provided by Art.

You need a table that contains enough rows to display all the dates. SYS.ALL_OBJECTS is a widely used table for such a purpose. It is accessible by anyone (unless you've changed the default privs of course) and contains a large number of rows. The ROWNUM pseudocolumn allows you to increment the date with every row you retrieve.

MHE
Re: How to get all dates between two dates in Oracle? [message #9781 is a reply to message #9763] Thu, 04 December 2003 04:31 Go to previous message
Cheeku
Messages: 17
Registered: November 2003
Junior Member
Hi Art,
Can you please explain the solutions you have provided for finding all the dates between two dates.I cud not understand it completely.(The use of rownum pseudocolumn)
Previous Topic: Urgent: Inserting Rows form a Table on Other Database
Next Topic: How do I retrieve a substring from a clob variable
Goto Forum:
  


Current Time: Thu Apr 25 08:31:41 CDT 2024