Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 10g
Oracle 10g [message #286610] Sat, 08 December 2007 23:42 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

I have a table which has three records.

date1 | date2
----- -----
05-10-07 | 15-10-07

10-10-07 | 20-10-07

01-10-07 | 03-10-07


Now i want to display all dates which ranges
in all dates like

DATE
----
01-10-07
02-10-07
03-10-07
05-10-07
06-10-07
.
.
15-10-07
.
.
20-10-07

please not that the output doesnt have 04-10-07.

Help me in the above regard

Thanks in Advance
Natesh
Re: Oracle 10g [message #286611 is a reply to message #286610] Sat, 08 December 2007 23:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search here for calendar.
By the way, did you read the link I posted in your previous topic, it tells you how to format your post and to give a test case.
An also, do you have any solution for this previous topic, we didn't any feedback from you?
If so, then please post it, this will help future readers that have the same problem.

Regards
Michel

[Updated on: Sun, 09 December 2007 00:00]

Report message to a moderator

To Display Range of Dates [message #286613 is a reply to message #286611] Sun, 09 December 2007 00:02 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Mic(exclusively)

I couldn't find accurate solution in the topic.
There are some. But i couldnt find out my correct
answer or i could find out half the answer of mine.

Related to formatting post, started reading the guide.

Hope i find a solution through the search word
"calendar", and post it down here.

One Personal thing about you. What are your Bed times?
Whenever i post, ur one among the replies. Smile

You are doing a great job & help.

Thanks & Regards
Natesh

[Updated on: Sun, 09 December 2007 00:06]

Report message to a moderator

Re: To Display Range of Dates [message #286616 is a reply to message #286613] Sun, 09 December 2007 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also think about posting a test case.
If you help people help you, you will likely have quicker and more complete answers.
If they have to spend time to build a test case, they have less time to find/build a solution and give explainations.

Regards
Michel
Re: To Display Range of Dates [message #286617 is a reply to message #286616] Sun, 09 December 2007 00:32 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Mic

Mic You are correct regarding test cases. Next time ill post a test cases too. Following in the given reply only. Smile.

CREATE TABLE TEMP_DATE_EX
(
  FRM_DATE  DATE,
  TO_DATE   DATE,
  EID       VARCHAR2(1 BYTE)
);


Actual data is as follows

insert into temp_Date_ex values('05-oct-07','15-oct-07','1')

insert into temp_date_Ex values ('10-oct-07','20-oct-07','1')

insert into temp_date_ex values ('01-oct-07','03-oct-07','1'
.

The required output is

FRM_DATE     TO_DATE
--------     -------
01-oct-07    03-oct-07
05-oct-07    20-oct-07
.

Based on the above scenario, i searched for a solution using
keyword "calendar". But, i couldnt find any. Sad.

Help me in the regard

Thanks
Natesh
Re: To Display Range of Dates [message #286619 is a reply to message #286617] Sun, 09 December 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
drop table t1 purge;
create table t1 as
select sysdate-3 date1, sysdate date2 from dual
union all
select sysdate-10, sysdate-5 from dual
union all
select sysdate-15,sysdate-13 from dual
/
alter session set nls_date_format='DD/MM/YYYY';

SQL> select * from t1 order by date1;
DATE1      DATE2
---------- ----------
24/11/2007 26/11/2007
29/11/2007 04/12/2007
06/12/2007 09/12/2007

3 rows selected.

SQL> with lines as (select level-1 line from dual connect by level<=100)
  2  select date1+line date1
  3  from t1, lines
  4  where line <= ceil(date2-date1)
  5  order by 1
  6  /
DATE1
----------
24/11/2007
25/11/2007
26/11/2007
29/11/2007
30/11/2007
01/12/2007
02/12/2007
03/12/2007
04/12/2007
06/12/2007
07/12/2007
08/12/2007
09/12/2007

13 rows selected.

The core of the query is a line generator that you'll find the "with" clause.
The rest is a classic join.

In addition, '05-oct-07' is not a date it is a string.
SQL> select to_date('05-oct-07') from dual;
select to_date('05-oct-07') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel

icon6.gif  Re: To Display Range of Dates [message #286621 is a reply to message #286619] Sun, 09 December 2007 01:15 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks a ton Mic!!

Your reply will really helps in my further proceedings.

One clarification.

SQL> select to_date('05-oct-07') from dual;


This query on my database is not resulting any error.
It is giving me output in SQL*PLUS and TOAD too,
even i have copied and pasted your SQL Query.

Can you figure out any reason.
Re: To Display Range of Dates [message #286626 is a reply to message #286621] Sun, 09 December 2007 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't have an error because your default date format and language match your string.
In French, 'oct' is meaningless in date context. And more, my default format is 'DD/MM/YYYY' (although Oracle is smart enough to translate DD-MON-RR to DD/MM/YYYY on the fly).
When you use TO_DATE, always specify a format. Never rely on default one (unless you force it as I did in my example).

Regards
Michel

[Updated on: Sun, 09 December 2007 02:37]

Report message to a moderator

Re: To Display Range of Dates [message #286698 is a reply to message #286626] Sun, 09 December 2007 22:15 Go to previous message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks one Again mic for the helpful information.
Previous Topic: Returning control to main-body from procedure.
Next Topic: problem with to_number
Goto Forum:
  


Current Time: Sat Dec 03 18:12:27 CST 2016

Total time taken to generate the page: 0.13177 seconds