Skip to main content

WORKDAYS_BETWEEN

Description

The WORKDAYS_BETWEEN function determines the amount of workdays between two given dates. When the start date is before the the end date, the end date is not counted. When the start date is after the end date, the start date is not counted and a negative value is returned.

This function only considers the dates of the given timestamps. The time of day does not influence the result.

The WORKDAYS_BETWEEN function retrieves its information regarding valid workdays from the passed Calendar configuration. For the weekday and factory calendars any day with a (non-empty) shift is considered a workday.

When either the start or the end date are outside of the scope of the calendar, NULL is returned. For the factory calendar the scope contains the complete year containing the first defined shift (so also all days before the first shift in that year), the complete year containing the last defined shift and all years in between.

WORKDAYS_BETWEEN follows the same logic as the WORKDAYS_BETWEEN function in SAP HANA.

Syntax

The recommended way of using the WORKDAYS_BETWEEN function is the following:

WORKDAYS_BETWEEN ( start_date, end_date, calendar_specification [, calendar_id_column])

  • start_date: Start date

  • end_date: End date

  • calendar_specification: One of WEEKDAY_CALENDAR, FACTORY_CALENDAR, WORKDAY_CALENDAR, or INTERSECT

  • calendar_id_column: Column to create a mapping between the respective activities and their used calendar specification. This is mandatory when using multiple calendar specifications. For more details, please take a look at the respective documentation of the DateTime Calendar.

Deprecated Behavior

With a configured calendar table the WORKDAYS_BETWEEN function can also be called in a different way. [DEPRECATED | Use the newest version of WORKDAYS_BETWEEN instead.]

The calendar table can be configured in the Data Model Editor and applies to all calls of WORKDAYS_BETWEEN in the Data Model.

The calendar table has to have the following layout:

  • Calendar ID: Identifier of the working calendar

  • Year: Year of the calendar

  • Month 1: Working days of January bit encoded. 1 is a working day. 0 is a day off. Example of January 2019 with the weekends off: 1111001111100111110011111001111

  • Month 2: Working days of February.

  • ...

  • Month 12: Working days of December

WORKDAYS_BETWEEN ( calendar_id, start_date, end_date [, INVALID_TO_NULL] )

  • calendar_id: The ID defines which calendar out of the calendar table is used.

  • start_date: Start date

  • end_date: End date

  • INVALID_TO_NULL: This flag prevents the fallback to DAYS_BETWEEN.

If the calendar ID does not exist in the calendar table or if one of the input dates lies outside of the range of the calendar, WORKDAYS_BETWEEN behaves like the DAYS_BETWEEN function and returns the number of days between the specified dates. In this case, a warning is issued.

When working with the deprecated version of this function, using the INVALID_TO_NULL flag will prevent the fallback to DAYS_BETWEEN, and NULL will be returned instead. This is the recommended behavior, since the fallback may lead to unexpected/unintuitive results.

Note

Please note that for the same two dates and the same WORKDAY_CALENDAR specification, there is a difference in using WORKDAYS_BETWEEN and subtracting two REMAP_TIMESTAMPS calls for these dates. WORKDAYS_BETWEEN always ignores the time of the day (e.g., 1970-01-01T23:59:59:999 is treated as 1970-01-01T00:00:00:000) while REMAP_TIMESTAMPS does not.

NULL handling

If any parameter is NULL, the result is NULL as well.

Exceptions

The new version of WORKDAYS_BETWEEN will not work if there are any gaps in the years covered by the respective WORKDAY_CALENDAR. An error will be returned if gaps are found in the workday calendar configuration.

Example