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
[1] In this scenario, the workday difference between two dates is determined using a workday calendar. In both examples the weekend on the sixth and seventh is not counted. Note that in the second example, even though the time of day of the start and end date are very different, the absolute value of the result stays the same. | |||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||
|
[2] The following example shows the behavior when either only the start or the end date is not a workday. As mentioned above, the input date at the later point in time is not counted. So when the start date is before the end date, the end date is counted, and when the end date is before the start date, the start date is not counted. This implies that: WORKDAYS_BETWEEN(start, end) = -WORKDAYS_BETWEEN(end, start) always holds. | |||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||
|
[3] The function also works with a weekday calendar. In the first example the third (Wednesday) is not counted. In the second example both the seventh (Sunday) and the third are not counted. | |||||||||||||
| |||||||||||||
|
[4] Additionally, a factory calendar or an arbitrary intersection of these calendars can be used. In the following scenario, the first example does not count the third (Wednesday), because it does not appear in the weekday calendar and the fifth (Friday), because it does not appear in the factory calendar. The second example does not count the seventh (Sunday) because even though the day appears in both calendars, the shift on the day do not overlap and so it is not contained in the intersection. | |||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||
|
[5] It is also possible to use different calendars for different rows by specifying an additional calendar id column. | ||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||
|