Home » SQL & PL/SQL » SQL & PL/SQL » how to find the number of days(ie the number of mondays) between 2 dates
how to find the number of days(ie the number of mondays) between 2 dates [message #202573] Fri, 10 November 2006 07:28 Go to next message
docetes
Messages: 5
Registered: November 2006
Junior Member
Hi there i've got a quick question, i've got two dates in the table below and a day field. I want to get the list of dates for the day between the 2 dates.

CREATE TABLE flight_length
(
flight_number INTEGER,
s_date DATE,
e_date DATE,
set_day CHAR(7),
);
DECLARE
startdate date;
enddate   date;
f_day CHAR(7); 

BEGIN

SELECT s_date INTO  startdate FROM flight_length where FLIGHT_NUMBER = 1234;

SELECT e_date INTO enddate FROM flight_length where FLIGHT_NUMBER = 1234;

SELECT set_day INTO  f_day FROM flight_length where FLIGHT_NUMBER = 1234;

-- ???


END;
/




sorry if this is in the wrong forum

Thanks for the help,
Dave

[Updated on: Fri, 10 November 2006 07:39]

Report message to a moderator

Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202578 is a reply to message #202573] Fri, 10 November 2006 07:41 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Check this out

http://asktom.oracle.com/~tkyte/Misc/DateDiff.html
Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202581 is a reply to message #202578] Fri, 10 November 2006 07:47 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
SELECT ROWNUM + s_date
  FROM ALL_OBJECTS, flight_length
 WHERE ROWNUM <= e_date - s_date - 1
   AND flight_number = 1234;
Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202598 is a reply to message #202573] Fri, 10 November 2006 08:46 Go to previous messageGo to next message
docetes
Messages: 5
Registered: November 2006
Junior Member
SELECT ROWNUM + s_date
  FROM  flight_length
 WHERE ROWNUM <= e_date - s_date - 1
   AND flight_number = 1234;


seems to work great thx but

BEGIN

SELECT ROWNUM + s_date
  FROM  flight_length
 WHERE ROWNUM <= e_date - s_date - 1
   AND flight_number = 1234;


END;
/


doesn't why is that?

thanks,
Dave
Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202607 is a reply to message #202598] Fri, 10 November 2006 09:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would be because the second one is a piece of invalid Pl/Sql, while the first is a piece of valid SQL.

In Pl/Sql, you need to a variable (or table) to select your results into. SOmething like:

DECLARE
  type ty_date_table is table of DATE index by binary_integer;

  t_table  ty_date_table;
BEGIN

 SELECT ROWNUM + s_date
 BULK COLLECT INTO t_table
 FROM   flight_length
 WHERE  ROWNUM <= e_date - s_date - 1
 AND    flight_number = 1234;

 DBMS_OUTPUT.PUT_LINE(t_Table.count||' results retrieved');
END;
/
Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202612 is a reply to message #202573] Fri, 10 November 2006 09:44 Go to previous messageGo to next message
docetes
Messages: 5
Registered: November 2006
Junior Member
i have set server output on but it doesn.t seem to displa them
Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202615 is a reply to message #202612] Fri, 10 November 2006 09:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The query I posted works for me.
Do you get an error when you run it?

Can you run it in SQL*Plus and cut and paste the results for us.
Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202617 is a reply to message #202612] Fri, 10 November 2006 09:58 Go to previous messageGo to next message
docetes
Messages: 5
Registered: November 2006
Junior Member
the result i get is

2 results retrieved

PL/SQL procedure successfully completed.
Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202624 is a reply to message #202617] Fri, 10 November 2006 10:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's exactly what the code is meant to print.
Why would you wrap the SQL in a BEGIN and END if you just wanted it to run as SQL?

It can be done (as everything can)
DECLARE
  type ty_date_table is table of DATE index by binary_integer;

  t_table  ty_date_table;
BEGIN

 SELECT ROWNUM + s_date
 BULK COLLECT INTO t_table
 FROM   flight_length
 WHERE  ROWNUM <= e_date - s_date - 1
 AND    flight_number = 1234;

 FOR idx IN t_table.first .. t_table.last LOOP
   DBMS_OUTPUT.PUT_LINE(to_char(t_Table(idx),'dd-mon-yyyy'));
 END LOOP;
END;

But you'd be much better off just running
 SELECT ROWNUM + s_date
 FROM   flight_length
 WHERE  ROWNUM <= e_date - s_date - 1
 AND    flight_number = 1234;
Re: how to find the number of days(ie the number of mondays) between 2 dates [message #202626 is a reply to message #202573] Fri, 10 November 2006 10:15 Go to previous message
docetes
Messages: 5
Registered: November 2006
Junior Member
cos thats just a tinny bit of what needs to be done and it has to be in a package and all that. Its a college assignment and that was the only bit i was stuck on.

Thx for the help
Previous Topic: Simplify this query
Next Topic: Is the line of code below redundant?
Goto Forum:
  


Current Time: Sun Dec 04 10:56:10 CST 2016

Total time taken to generate the page: 0.08962 seconds