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>

 

Advertisements