How to generate a list of dates


it’s been a long time since the my last article in this BLOG. But from today on I want to reactivate my blog. I’ll start with an article on how to generate a list of rows, e.g. if you want to generate a sequential list of dates between two given dates (e.g. 1st of January, 2015 and 31st of December 2015). You have the need to generate 365 rows and each is incremented by one day.

To do that within Oracle SQL you can just use the concepts of hierarchical queries, in detail that’s the connect by statement which just tells the dbms in a hierarchical query how the different levels are connected (i.e. a child and parent key). In our case we will use it to generate a new row for each level and the level in our case is the counter for each row. We are connecting every row to a parent until we reach a hierarchy depth of e.g. 365. That’s why it is working in this case.

Here comes the code:

SELECT (to_Date(‚01.01.2015‘, ‚DD.MM.YYYY‘) + (LEVEL -1)) AS GEN_DATE
CONNECT BY LEVEL <=to_date(‚31.12.2015‘, ‚DD.MM.YYYY‘)-   to_date(‚01.01.2015‘, ‚DD.MM.YYYY‘)+1;

With the expression to_date(‚31.12.2015‘, ‚DD.MM.YYYY‘)-to_date(‚01.01.2015‘, ‚DD.MM.YYYY‘) we just tell the database that we want to generate  the dates between the given dates. If you subtract two dates you receive the difference in days. We need one more otherwise the last given date is missing. In the select we just take the given start date and the level is added. As the level counts up by one with each new line we always add one more in each line. Adding a number to a date is just adding a day. And in this way we generate a list of the dates between two given dates.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.