CODEWITHSUNDEEP
sqloracle-databasedate
ThomasS
ThomasS

Reputation: 715

Select days between given start and end date

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

Upvotes: 1

Views: 289

Answers (1)

user330315
user330315

Reputation: 

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;
/

Upvotes: 2

Related Questions