IN_CALENDAR
Description
IN_CALENDAR checks whether a given date is within a calendar.
Supported input column types: timestamp_column -> DATE
Output column type: INT
If the timestamp is outside of the scope of the given workday or factory calendar(s), NULL is returned. For a 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.
Syntax
IN_CALENDAR ( timestamp_column, calendar_specification [, calendar_id_column])
timestamp_column: timestamp
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.
NULL handling
If any parameter is NULL, the result is NULL as well.
Exceptions
The IN_CALENDAR 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.
Examples
[1] A simple IN_CALENDAR example using a weekday calendar. The first of January 2018 is a Monday. | ||||||||||||||||||
| ||||||||||||||||||
|
[2] A simple IN_CALENDAR example using a workday calendar. The first of January 2017 is a Sunday. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[3] IN_CALENDAR can also be used with an intersection of different calendars. Only the timestamps that are covered by both calendars are taken into account. | ||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||
|
[4] It is also possible to use different calendars for different rows by specifying an additional calendar id column. | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|