Create Dates Table on Exasol using SQL to List Days Between Two Dates

In this Exasol SQL tutorial, database developers can see how they can return days between two date values using SQL functions by creating a dates table on Exasol. SQL programmers will also see how to use Dual table with Connect by Level clause, Add_Days and Days_Between SQL dates function.

Following SQL Select statement on DUAL table with CONNECT BY LEVEL enables the return of rows in number specified by the output of SQL Dates function Days_Between(). Exasol dates function days_between returns 11 days between 12th of May and 1st of May, so we add 1 more to this calculation. At the end the SELECT from DUAL table will have 12 rows.

We can decide to choose the values or fields of the output resultset.
For example, by using Add_Days() SQL dates function, we can add the numeric value "level" as incrementals days count as an argument to this Exasol dates function.


 select

  add_days(date '2020-05-01', level-1) as dates

 from dual

 connect by level <= days_between('2020-05-12','2020-05-01')+1

 order by local.dates;

Code

SQL developers can also realize the use of local clause which is referencing to the calculated field "dates" in SELECT list and enables use of the field which is in select list again in ORDER BY clause by only referincing to its alias.

Here is our list showing the days between given two date values on Exasol database by SQL

SQL dates table on Exasol database

If you have not yet used the dual table, you can simply execute following SQL script to have a basic idea about how it works and for what it can be used.


 select * from dual;

Code

I can guess the output did not provide a hint for SQL programmers who are using the DUAL table for the first time.

Exasol SQL databae dual table

In fact, dual table on Exasol is very similar for use as DUMMY table on many other database platforms like Oracle, SAP HANA database, etc.
One reason it exists on Exasol is for compability between other data platforms.

On the other hand, database developers can use DUAL table with Connect By Level clause which is very helpful to data professionals in many cases.
Let's execute following SQL SELECT on dual table


 select level from dual connect by level <= 10;

Code

By excluding the dummy column from the SELECT fields list and adding the level value as a new column, actually database developers can have a simple yet effective SQL numbers table on Exasol Analytic Data Warehouse.

Exasol database SQL numbers table

I hope this tips on Duals table to create dates table on Exasol database for SQL developers is useful for all.