ADD_WORKDAYS
Description
The ADD_WORKDAYS function adds a given number of workdays to a given date. If the start date is not a workday, ADD_WORKDAYS returns the same result as if the start date was the next workday instead (even when 0 days are added).
This function only modifies the date of the given timestamps and does not change the time of day.
The ADD_WORKDAYS 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.
If the computation leaves the scope of the calendar, NULL is returned. This is also the case when the start date lies outside of the scope of the calendar. 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.
ADD_WORKDAYS follows the same logic as the ADD_WORKDAYS function in SAP HANA.
Syntax
The recommended way of using the ADD_WORKDAYS function is the following:
ADD_WORKDAYS ( date, number_of_days, calendar_specification [, calendar_id_column])
date: Start date
number_of_days: Number of workdays which are added to the start date (can be negative)
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.
Legacy Behavior
There is also a legacy way of using the ADD_WORKDAYS function. Here it is only possible to specify a workday calendar.
ADD_WORKDAYS ( workday_calendar_config, date, number_of_days )
workday_calendar_config: WORKDAY_CALENDAR to use. A TFACS entry identifier is required.
date: Base date.
number_of_days: Number of working days which are added to the base date.
Deprecated Behavior
The ADD_WORKDAYS function can also be called as follows: [ DEPRECATED | Use the newest version of ADD_WORKDAYS instead.]
ADD_WORKDAYS ( <calendar_id>, <date>, <number_of_days> [, INVALID_TO_NULL] )
calendar_id: Defines which calendar entries out of the TFACS table are used.
date: The base date.
number_of_days: The number of working days which are added to the base date.
INVALID_TO_NULL: This flag prevents the fallback to ADD_DAYS.
In this case, the calendar table needs to have the same layout as for WORKDAYS_BETWEEN. Note that the TFACS table and workday calendars were set during data load.
If the calendar ID does not exist in the calendar table, or if the scope of the calendar would have been left by the computation (i.e., adding given days to the date would have a result which is not covered by the calendar), ADD_WORKDAYS behaves like the ADD_DAYS function and adds the specified number of days to the date. The result of ADD_DAYS, however, may still be in the scope of the calendar. 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 ADD_DAYS, and NULL will be returned instead. This is the recommended behavior, since the fallback may lead to unexpected/unintuitive results.
Null Handling
If any parameter is NULL, the result is NULL
Exceptions
The ADD_WORKDAYS operator does 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.
Differences Between the Newest and the Legacy Versions
If the start is not a workday and a positive amount of days is added, the newest version will return the first workday which comes after the result of the legacy versions. Additionally, if the start date is out of bounds of the given calendar, but the first date after/before (when adding/subtracting days) is inside the bounds of the given calendar, the newest version will return NULL, while the legacy versions will return a result.
Example
[1] In this first test scenario, workdays are added and subtracted using a workday calendar. In the first two examples the weekend on the sixth and seventh is skipped. In the last two examples the start is not a workday. | |||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||
|
[2] If the start is not a workday, adding 0 days returns the next workday. | |||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||
|
[3] The function also works with a weekday calendar. In the first example the third (Wednesday) is skipped. In the second example both the seventh (Sunday) and the third are skipped. | |||||||||||||
| |||||||||||||
|
[4] Additionally, a factory calendar or an arbitrary intersection of the different calendars can be used. In the following scenario, the first example skips over 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 skips over the seventh (Sunday) because even though the day appears in both calendars, the shifts 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. | |||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||
|
[6] In this example which makes use of the legacy version, ADD_WORKDAYS is used with a workday calendar based on a TFACS table for the year 2018. We use constants as input and add one workday to the 29th of March 2018. As the 30th of March is a public holiday in Bavaria Germany, the 31st is on the weekend, the 1st of April 2018 is also on the weekend and the 2nd of April is yet again a public holiday, we get 3rd of April 2018 as result. | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|