Generating a series of sequential dates in Oracle can be done in various ways and information for this can be found on multiple sites .
I ran into a situation that for a report we needed to;
– schedule it every day at 3 AM
– billing the customer at the end of each month (at the 1. of the following month)
The report already contained the date range generator however on each first of the month it would take the “new” month and not the previous month and therefore charging the customer was not possible as it would mis the last day of the previous month.
The solution was actually rather simple yet very convenient.
</pre> WITH PARAMS AS (SELECT Case when to_char(SYSDATE, 'DD')=01 then (sysdate-1) else (sysdate) END AS DATESEL FROM DUAL ), DATE_LIST AS (SELECT TRUNC(params.datesel, 'MM') + LEVEL - 1 AS TDAY FROM DUAL,params CONNECT BY TRUNC(TRUNC(params.datesel, 'MM') + LEVEL - 1, 'MM') =TRUNC(params.datesel, 'MM') ) Select * from date_list; <pre>