List of days between two dates in Exasol

Question

In PostgreSQL, it's fairly easy to generate a list of all days between two dates:

>>> select generate_series('2013-01-01'::date, '2013-01-06'::date, '1 day'::interval)::date i;

i
2013-01-01
2013-01-02
2013-01-03
2013-01-04
2013-01-05
2013-01-06

Is it possible to achieve the same result in Exasol?

score 2 · Accepted Answer · answered Apr 19 '20 at 11:55

2

You can use connect by to generate numbers and then convert that to dates:

with n as (
      select level - 1 as n
      from dual
      connect by level < 6
     )
select add_days(date '2013-01-01', n.n)
from n;

answered Apr 19 '20 at 11:55

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 2 · answered May 09 '20 at 14:53

Following SQL SELECT can be used for returning days list within the given interval similar to Gordon's solution as described at create dates table on Exasol article

select
 add_days(date '2013-01-01', level-1) as dates
from dual
connect by level <= days_between('2013-01-06','2013-01-01')+1
order by local.dates;

List of days between two dates in Exasol

2 Answers2