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
Using it in automation tasks
To support automation tasks, you can use the behavior of mode in combination with leaving parameters null
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.