Pages

Friday, December 19, 2014

DATE row generator with DBMS_SCHEDULER

A recent question on the Oracle-L mailing list was about generating dates in a given period. If you had a string like 'MWF', all Mondays, Wednesdays and Fridays between the from- and to-dates should be generated.

That reminded me of a quiz on plsqlchallenge.com I did on using DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING. I had created a table function to test calendar expressions - that might be useful for something similar to what was asked on the mailing list.

So here's a way to generate rows of DATEs using DBMS_SCHEDULER calendaring syntax.

I start by setting my session NLS_DATE_FORMAT - just so that the following queries show clearly what date values has been generated:

Notice how the above was specified 2015-01-04 as end date, but no results were given for 2015-01-04? Well, that's because the end date was at midnight. When using time in the expressions I would also need to be aware of time in the start and end date parameters:

That expression is for 18:30 o'clock on the second tuesday of every other month (as I start with february, that then becomes even months. (Very important schedule, as those are the times that my Beer Enthusiast Association usually meets ;-)

The table function method using DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING can be helpful to generate rows of DATEs when you need some rules about which dates to generate. But if you just need a simple list of all dates between start and end, that would be overkill and the SELECT FROM DUAL method will be much more efficient.