Home » SQL & PL/SQL » SQL & PL/SQL » Date list between two dates without base table (Oracle 10g)
Date list between two dates without base table [message #349465] Mon, 22 September 2008 00:15 Go to next message
bernandas
Messages: 16
Registered: June 2007
Location: Chennai
Junior Member
Dear all,

i want to list out the dates between two dates from query itself. (no base tables). is there any built-ins available.
Pls suggest me. thanks in advance.

Re: Date list between two dates without base table [message #349467 is a reply to message #349465] Mon, 22 September 2008 00:18 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You mean to say Days between two dates ?

Select date1 - date2 from dual ;

Thumbs Up
Rajuvan.

[Updated on: Mon, 22 September 2008 00:18]

Report message to a moderator

Re: Date list between two dates without base table [message #349468 is a reply to message #349467] Mon, 22 September 2008 00:20 Go to previous messageGo to next message
bernandas
Messages: 16
Registered: June 2007
Location: Chennai
Junior Member
Not the days between two dates.

pls reffer the below example

Ex:

Input 1 : 01-Apr-2008
Input 2 : 05-Apr-2008

Required Output
---------------
02-Apr-2008
03-Apr-2008
04-Apr-2008
Re: Date list between two dates without base table [message #349470 is a reply to message #349465] Mon, 22 September 2008 00:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Use Connect by to achieve the same . Add the level to the date filed to increment .

Thumbs Up
Rajuvan.
Re: Date list between two dates without base table [message #349472 is a reply to message #349470] Mon, 22 September 2008 00:29 Go to previous messageGo to next message
bernandas
Messages: 16
Registered: June 2007
Location: Chennai
Junior Member
Ok, Thanks. il try and update the thread
Re: Date list between two dates without base table [message #349476 is a reply to message #349465] Mon, 22 September 2008 00:47 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
select to_date('01-jan-2008','dd-mon-yyyy')+level from dual
connect by level<to_Date('10-jan-2008','dd-mon-yyyy')-to_date('01-jan-2008','dd-mon-yyyy')

something like that??
Re: Date list between two dates without base table [message #349482 is a reply to message #349476] Mon, 22 September 2008 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide.

1/
"How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

2/
Don't put solution only hint or clue, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel

Re: Date list between two dates without base table [message #349489 is a reply to message #349465] Mon, 22 September 2008 01:28 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
we can achieve this by this procedure also

CREATE OR REPLACE PROCEDURE dateDiff
( date1 IN DATE,
date2 IN DATE,
days out varchar2)
AS
BEGIN
select (date1-date2) into days from dual;
dbms_output.put_line(days);
END;

then execute this procedure
Re: Date list between two dates without base table [message #349501 is a reply to message #349465] Mon, 22 September 2008 01:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ monikabhakuni,

It is General Practice to use SQL instead of PL/SQL whenever possible.

Secondly , This is not the Answer expected by OP. He/She Needs to list out the Days in between and not the days between two dates .

Thumbs Up
Rajuvan.

[Updated on: Mon, 22 September 2008 02:22]

Report message to a moderator

Re: Date list between two dates without base table [message #349513 is a reply to message #349489] Mon, 22 September 2008 02:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@monikabhakuni

Read my previous and folow guidelines.

Regards
Michel
Previous Topic: Convertion from Number to Word
Next Topic: BULK COLLECT INTO with limit?
Goto Forum:
  


Current Time: Sat Dec 03 01:21:26 CST 2016

Total time taken to generate the page: 0.09041 seconds