Home » SQL & PL/SQL » SQL & PL/SQL » dates between two dates using select statement (Oracle 9i, Ver.9.2.0.4.0, Linux 9)
dates between two dates using select statement [message #362784] Thu, 04 December 2008 02:29 Go to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

In Oracle 10g I can get the result I wanted, but in Oracle 9i is there any way to get the result?
For example in Oracle 10g
select sysdate, sysdate+level from dual connect by level<=10

Result
SYSDATE   SYSDATE+L
--------- ---------
04-DEC-08 05-DEC-08
04-DEC-08 06-DEC-08
04-DEC-08 07-DEC-08
04-DEC-08 08-DEC-08
04-DEC-08 09-DEC-08
04-DEC-08 10-DEC-08
04-DEC-08 11-DEC-08
04-DEC-08 12-DEC-08
04-DEC-08 13-DEC-08
04-DEC-08 14-DEC-08

Thanks and Regards,
MSMallya
Re: dates between two dates using select statement [message #362785 is a reply to message #362784] Thu, 04 December 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Push the "select from dual connect by" in a subquery.

Regards
Michel
Re: dates between two dates using select statement [message #363137 is a reply to message #362785] Fri, 05 December 2008 03:21 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

Thanks Michel for your speedy reply!!!!

I have tried what you have suggested and working.
When I tried this on 9.0.1.1.1 it returns errors as follows
select sysdate, dt from (select sysdate+level dt from dual connect by level<10)
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected

Actually the problem was not in Oracle 9i 9.2.0.4.0 as I had mentioned, but it was in Oracle9i 9.0.1.1.1 version.

Is there any way out to get the desired result in 9.0.1.1.1?

Thanks again.

MSMallya
Re: dates between two dates using select statement [message #363141 is a reply to message #363137] Fri, 05 December 2008 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have no 9.0 version to check (and actually I recommend you to change as this version was the worst Oracle ever delivered) but you can always the old all_objects trick to get rows.

Regards
Michel
Re: dates between two dates using select statement [message #363236 is a reply to message #362784] Fri, 05 December 2008 08:23 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Try this.

SELECT TRUNC (SYSDATE), TRUNC (SYSDATE + ROWNUM)
FROM (SELECT 1
FROM DUAL
GROUP BY CUBE (1, 2, 3, 4, 5))
WHERE ROWNUM <= 10;


Best Regards.
Muhammad Asif Malik.
Re: dates between two dates using select statement [message #363244 is a reply to message #363236] Fri, 05 December 2008 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gee! I wrote a full performances test case on this in a topic here and I forgot it; I can't believe it!

Regards
Michel
Re: dates between two dates using select statement [message #363270 is a reply to message #362784] Fri, 05 December 2008 11:43 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michel Uncle.

I am unable to get your point. I am not understanding ,what you are saying. please explain.

Best Regards.
Muhammad Asif Malik.
Re: dates between two dates using select statement [message #363279 is a reply to message #362784] Fri, 05 December 2008 12:04 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

.

[Updated on: Fri, 05 December 2008 12:06]

Report message to a moderator

Re: dates between two dates using select statement [message #363281 is a reply to message #363270] Fri, 05 December 2008 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just that this is a good solution.

Regards
Michel
Re: dates between two dates using select statement [message #364588 is a reply to message #363236] Tue, 09 December 2008 03:59 Go to previous message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

Really a good solution! Thanx!!!

Regards,
MSMallya
Previous Topic: Need Help on Loop Program in PL/Sql
Next Topic: Database trigger question (merged)
Goto Forum:
  


Current Time: Thu Dec 08 18:43:54 CST 2016

Total time taken to generate the page: 0.08742 seconds