Helper for calculating periods for queries and tasks

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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

en English
X