1

I need to generate a list of days between 2 given dates. The query should be something like

Select ... from ... where "date" is between date1 and date2

And the response for '2014-05-12' and '2014-05-16' should be

Monday   | 2014-05-12
Tuesday  | 2014-05-13
Wednesday| 2014-05-14
Thursday | 2014-05-15
Friday   | 2014-05-16

The start and end date will never be more then 1 month apart and will always be in the future. I would preferably not make a table or view with all the dates in, but if thats the only solution...

I found generate days from date range but the given query gave an error and i dont understand it at all so can't start to find what might cause the error.

Thanks for the help

Community
  • 1
  • 1
ThomasS
  • 705
  • 1
  • 11
  • 30

1 Answers1

2
with days as (
   select date '2014-05-12' + (level - 1) as day
   from dual 
   connect by level <= date '2014-05-16' - date '2014-05-12' + 1
) 
select *
from days
order by day;

SQLFiddle: http://sqlfiddle.com/#!4/d41d8/29558

If you need this on a regular basis, it's probably better to create a function that does this for you:

create or replace type date_list as table of date
/

create or replace function generate_series(p_start_date date, p_end_date date)
  return date_list
  pipelined
is
  l_count integer;
begin
  l_count := trunc(p_end_date) - trunc(p_start_date);
  for i in 0..l_count loop
    pipe row (p_start_date + i);
  end loop;
end;
/
  • 1
    @Thomass: Yes, it returns `DATE` values which can be formatted using `to_char()`. See the manual for details: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions200.htm#i1009324 –  May 13 '14 at 13:30