Skip to Main Content
  • Questions

  • Get all dates between dates in a Table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Paul.

Asked: April 22, 2016 - 1:31 am UTC

Last updated: April 29, 2016 - 10:23 am UTC

Version: ApEx 4.2

Viewed 1000+ times

You Asked

Hi,

I have dates (START_DATE and END_DATE) stored in a table called RMP_CAPACITY_DATES

What I need to do is query the table, and get all the dates from the START_DATE to END_DATE (inclusive) for each row in the table, and store them into a page item in the format yyyy-mm-dd, yyyy-mm-dd, yyyy-mm-dd... etc

So for example if the table had the following

START_DATE END_DATE
01/MAR/16 3/MAR/16
7/MAR/16 8/MAR/16

Then the query would return

2016-03-01, 2016-03-02, 2016-03-03, 2016-03-07, 2016-03-08

How do I go about creating this

Thanks

and Chris said...

Here's a 2 step process:

- Create of all the possible dates from the first start to the last end
- Join this to your table where the date is between the start and end for the row

Use the formatting options on to_char to control how you want the dates displayed:

create table t ( st_dt date, en_dt date );

insert into t values (date'2016-03-01', date'2016-03-03');
insert into t values (date'2016-03-07', date'2016-03-08');

with dts as (
  select date'2016-03-01' + rownum - 1 d from dual
  connect by level <= (select max(en_dt)-min(st_dt)+1 from t)
)
  select to_char(d, 'yyyy-mm-dd') dt
  from   dts
  join   t
  on     d between st_dt and en_dt;

DT       
----------
2016-03-01
2016-03-02
2016-03-03
2016-03-07
2016-03-08

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Paul Ferris, April 26, 2016 - 3:32 am UTC

Thanks for the response.

I had to modify the query slightly so that it would work as needed

create table t ( st_dt date, en_dt date );

insert into t values (date'2016-03-01', date'2016-03-03');
insert into t values (date'2016-03-07', date'2016-03-08');

with dts as (
select date'2016-03-01' + rownum - 1 d from dual
connect by level <= (select max(en_dt)-min(st_dt)+1 from t)
)
select LISTAGG(to_char(d, 'yyyy-mm-dd'), ',' ) WITHIN GROUP (ORDER BY d) dt
from dts
join t
on d between st_dt and en_dt;

I am also finding that if I delete a row in the table holding the dates, then the query will return no results. After doing a delete on a row, I need to have a dummy row in order to return any results
Connor McDonald
April 26, 2016 - 2:39 pm UTC

What do you want return when there's no rows in t?

Paul Ferris, April 26, 2016 - 9:48 pm UTC

Maybe just a dummy date

1990-01-01
Chris Saxon
April 29, 2016 - 10:23 am UTC

If there's nothing in t, your query will still return row with a null value. So you can nvl your listagg to show something else:

drop table t purge;
create table t ( st_dt date, en_dt date ); 

with dts as ( 
select date'2016-03-01' + rownum - 1 d from dual 
connect by level <= (select max(en_dt)-min(st_dt)+1 from t) 
) 
select nvl(
         LISTAGG(to_char(d, 'yyyy-mm-dd'), ',' ) WITHIN GROUP (ORDER BY d),
         '1990-01-01'
       ) dt 
from dts 
join t 
on d between st_dt and en_dt; 

DT                                                                                                                                                             
-----------
1990-01-01

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.