To prevent all kinds of different calculations I’ve made a method inside the database that can be used inside custom queries.
SYS_DATES_TO_PERIOD(IN_FROM D_DATE, IN_TO D_DATE, IN_MODE D_PERIOD_MODE)
As you might notice , the input parameters are not date-times, but only dates. This is because on end-user level people are used to tell you “Jan 1st” until “Jan 31st”. In fact they mean “2020-01-01 06:00” until “2020-02-01 06:00”.
Modes in period mode
Possible values for mode are
- WorkDays
- CurrentMonth
- LastMonth
- NextMonth
- CurrentWeek
- LastWeek
- NextWeek
- CurrentYear
- LastYear
- NextYear
- YearToDate
Using it in automation tasks
To support automation tasks, you can use the behavior of mode in combination with leaving parameters null
For example
If you run a task every first of the month to do an export of last month, just put Mode on LastMonth and leave IN_FROM on null. The procedure will replace in_from with current_date and the behavior is as you expected.
You must log in to post a comment.