Dataphin:Built-in functions of public calendar

Limits

Available only in offline SQL code.

Directory of built-in functions

The public calendar supports the following built-in functions:

dpc_last_workday

• Command syntax:

  string dpc_last_workday(string "calendar_code", string "business_date", int day_offset=1)

• Command description:

  Computes the nearest workday to the given business_date based on the day_offset.

• Parameter description:

  • calendar_code: Identifier for the public calendar, String type.

  • label_code: Identifier for the label, String type.

  • business_date: Any business date, String type, in the format yyyyMMdd.

  • periods: The number of cycles, Integer type, with a default of 1. Valid ranges are 1d <= day_offset <= 7305d (20 years), 1m <= day_offset <= 240m, or 1y <= day_offset <= 20y.

  • timeFormat: The type of cycle, with a default of d. Permitted types are:

    • Day: d.

    • Month: m.

    • Year: y.

• Return value description:

  Returns all working days with the label_code tag within the periods day/month/year cycles closest to business_date before business_date in the public calendar calendar_code. If business_date itself is a working day, then business_date is considered the 1st working day.

• Example:

  select dpc_last_labelled_workdays('finance_calendar','chrismas_day' ,'20231126'); --Returns 20231124
  select dpc_last_labelled_workdays('finance_calendar', 'chrismas_day' ,'20231127'); --Returns 20231127
  select dpc_last_labelled_workdays('finance_calendar', 'chrismas_day' ,'20231127', 5); --Returns 20231121,20231122,20231123,20231124,20231125
  select dpc_last_labelled_workdays('finance_calendar', 'chrismas_day' ,'20231127', m, 1); --Returns 20231030...20231127

dpc_last_label_date

• Command syntax:

  string dpc_last_label_date(string "calendar_code", string "business_date", string "label_code", int day_offset=1)

• Command description:

  Finds the closest date with the label_code to the business_date, based on the day_offset.

• Parameter description:

  • calendar_code: Identifier for the public calendar, String type.

  • business_date: A String representing any business date, formatted as yyyyMMdd.

  • label_code: Identifier for the label, String type.

  • day_offset: The number of days to the nearest date, Integer type, with a default of 1. Valid ranges are 1 <= day_offset <= 7305 (20 years).

• Return value description:

  Returns the nearest day_offset date with the label label_code before the business_date in the public calendar calendar_code. If the business_date itself contains the label label_code, then business_date is the first date. Return type is String, format is yyyyMMdd.yyyyMMdd

• Example:

  select dpc_last_label_date('finance_calendar', '20231230', 'chrismas_day'); --Returns 20231225
  select dpc_last_label_date('finance_calendar', '20231225', 'chrismas_day'); --Returns 20231225
  select dpc_last_label_date('finance_calendar', '20231230', 'chrismas_day', 2); --Returns 20221225

dpc_is_labelled

• Command syntax:

  boolean dpc_is_labelled(string "calendar_code", string "business_date", string "label_code")

• Command description:

  Checks whether the business_date in the public calendar calendar_code is associated with the label_code.

• Parameter description:

  • calendar_code: The code representing the public calendar, String type.

  • business_date: A String representing any business date, formatted as yyyyMMdd.

  • label_code: The label identifier, String type.

• Return value description:

  Returns whether the business_date in the public calendar calendar_code carries the label_code. Returns True if the label is present, otherwise False.

• Example:

  select dpc_is_labelled('finance_calendar', '20231225', 'chrismas_day'); --Returns True

dpc_is_workday

• Command syntax:

  boolean dpc_is_workday(string "calendar_code", string "business_date")

• Command description:

  Checks whether the business_date in the public calendar calendar_code qualifies as a workday.

• Parameter description:

  • calendar_code: The code for the public calendar, String type.

  • business_date: A String representing any business date, in the format yyyyMMdd.

• Return value description:

  Returns whether the business_date in the public calendar calendar_code is a workday. Returns True for a workday, otherwise False.

• Example:

  select dpc_is_workday('finance_calendar', '20231128'); --Returns True
  select dpc_is_workday('finance_calendar', '20231202'); --Returns False

dpc_last_workdays

• Command syntax:

  string dpc_last_workdays(string <calendar_code>, string <business_date>, int <periods>, string <timeFormat>)

• Command description:

  Calculates all working days within the periods day/month/year cycles closest to business_date before business_date in the public calendar calendar_code.

• Parameter description:

  • calendar_code: The code for the public calendar, String type.

  • business_date: A String representing any business date, in the format yyyyMMdd.

  • periods: The number of cycles, Integer type, with a default of 1. Valid ranges are 1d <= day_offset <= 7305d (20 years), 1m <= day_offset <= 240m, or 1y <= day_offset <= 20y.

  • timeFormat: The cycle type, with a default of d. Allowed types are:

    • Day: d.

    • Month: m.

    • Year: y.

• Return value description:

  Returns all working days within the periods day/month/year cycles closest to business_date before business_date in the public calendar calendar_code. If business_date itself is a working day, then business_date is considered the first working day.

• Example:

  select dpc_last_workdays('finance_calendar', '20231126'); --Returns 20231124
  select dpc_last_workdays('finance_calendar', '20231127'); --Returns 20231127
  select dpc_last_workdays('finance_calendar', '20231127', 5); --Returns20231121,20231122,20231123,20231124,20231125
  select dpc_last_workdays('finance_calendar', '20231127', m, 1); --Returns 20231030...20231127

dpc_workdays

• Command syntax:

  string dpc_workdays(string <calendar_code>, string <startdate>, string <enddate>)

• Command description:

  Lists all workdays from the startdate to the enddate (inclusive) within the public calendar calendar_code.

• Parameter description:

  • calendar_code: The code representing the public calendar, String type.

  • startdate: A String representing any start business date, with the format yyyyMMdd.

  • enddate: Specifies any end business date as a String in the yyyyMMdd format.

    Important

    The valid range is enddate - startdate <= 20 years.

• Return value description:

  Returns all workdays between startdate (inclusive) and enddate (inclusive) in the public calendar calendar_code. If the business_date itself is a workday, then business_date is counted as the first workday.

• Example:

  select dpc_workdays('finance_calendar', '20231124', '20231126'); --Returns 20231124
  select dpc_workdays('finance_calendar', '20231126', '20231127'); --Returns 20231127

dpc_last_labelled_workdays

• Command syntax:

  string dpc_last_labelled_workdays(string <calendar_code>, string <label_code>, string <business_date>, int <periods>, string <timeFormat>)

• Command description:

  Calculates all working days with the label_code tag within the periods day/month/year cycles closest to business_date before business_date in the public calendar calendar_code.

• Parameter description:

  • calendar_code: The code for the public calendar, String type.

  • label_code: The code for the label, String type.

  • business_date: Any business date, String type, in the format yyyyMMdd.

  • periods: The number of cycles, Integer type, with a default of 1. Valid ranges are 1d <= day_offset <= 7305d (20 years), 1m <= day_offset <= 240m, or 1y <= day_offset <= 20y.

  • timeFormat: The type of cycle, with a default of d. Permitted types are:

    • Day: d.

    • Month: m.

    • Year: y.

• Return value description:

  Returns all working days with the label_code tag within the periods day/month/year cycle closest to business_date before business_date in the public calendar calendar_code. If business_date itself is a working day, then business_date is considered the first working day.

• Example:

  select dpc_last_labelled_workdays('finance_calendar','chrismas_day' ,'20231126'); --Returns 20231124
  select dpc_last_labelled_workdays('finance_calendar', 'chrismas_day' ,'20231127'); --Returns 20231127
  select dpc_last_labelled_workdays('finance_calendar', 'chrismas_day' ,'20231127', 5); --Returns 20231121,20231122,20231123,20231124,20231125
  select dpc_last_labelled_workdays('finance_calendar', 'chrismas_day' ,'20231127', m, 1); --Returns 20231030...20231127

dpc_labelled_workdays

• Command syntax:

  string dpc_labelled_workdays(string <calendar_code>, string <label_code>, string <startdate>, string <enddate>)

• Command description:

  Computes all workdays tagged with calendar_code from startdate through enddate, inclusive, within the specified public calendar calendar_code.

• Parameter description:

  • calendar_code: The identifier for the public calendar, String type.

  • label_code: The identifier for the label, String type.

  • startdate: Specify any start business date as a String in the yyyyMMdd format.

  • enddate: A String representing any end business date, in the format yyyyMMdd.

    Important

    The valid range is enddate - startdate <= 20 years.

• Return value description:

  Returns all workdays with the label lable_code between startdate (inclusive) and enddate (inclusive) in the public calendar calendar_code. If the business_date itself is a workday, then business_date is counted as the first workday.

• Example:

  select dpc_labelled_workdays('finance_calendar','chrismas_day','20231124', '20231126'); --Returns 20231124,20231126
  select dpc_labelled_workdays('finance_calendar','chrismas_day', '20231126', '20231127'); --Returns 20231126,20231127