15.00 - About Business Calendar Macros - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

About Business Calendar Macros

System business macros enable administrators and users to configure the system business calendars (Teradata, ISO, and COMPATIBLE) by:

  • Creating the pattern for the calendar.
  • Creating exceptions for the calendar.
  • Deleting existing exceptions from the calendar.
  • These macros are created when the DIP script is executed and reside in the DBC database.

    Note: To view any of the macros, use the DIP script.

     

    Macro Name

    Purpose

    CreateBusinessCalendarPattern

    Creates the pattern for a business calendar.

    CreateException

    Defines an exception list for a specific business calendar. The list identifies all of the days of the calendar that are exceptions to the pattern defined for the calendar.

    DeleteException

    Deletes a particular exception from a specific business calendar.

    DeleteAllExceptions

    Deletes all of the exceptions from a specific business calendar.

    CreateBusinessCalendarPattern

    You can use this macro to create the pattern for a business calendar. The pattern is the template for business days and non-business days for the entire calendar and the basic structural unit on which the calendar is based (for example, week, month, quarter or year).

    Note: Currently, only the weekly pattern is supported. You can define each day of the week as a business day or non-business day. To make particular business days non-business days (for example, a holiday), you can use the CreateException macro (see “CreateException” on page 386).

    When you execute the macro, the pattern for the calendar is created and the information about the pattern is stored in the DBC.BusinessCalendarPattern table.

    Required Parameters

     

    Parameter

    Data Type

    Format

    Description

    CalendarName

    VARCHAR(256) CHARACTER SET UNICODE

    NOT CASESPECIFIC

    NOT NULL

    X(128)

    Name of the business calendar. For the system business calendars, it must be Teradata, ISO, or COMPATIBLE.

    DayName

    CHAR(10)

    X(10)

    Name of the calendar day. Must be SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, or SATURDAY.

    Pattern

    CHAR(5)

    X(5)

    Pattern for the calendar day. Must be ON (working day) or OFF (non-working day).

    PatternComment

    VARCHAR(2048)

    X(1024)

    Reason or rationale for the pattern for the calendar day (ON or OFF).

    Example  

    In the following, Saturday and Sunday of each week of the Teradata business calendar are defined as non-working days (non-working days have the OFF status) and this information is added to the DBC.BusinessCalendarPattern table.

    Note: By default, each day of week for the Teradata business calendar is a working day.

    EXEC DBC.CreateBusinessCalendarPattern
       ('TERADATA', ‘SATURDAY’,’OFF’, ‘Non-work Day’);
     
    EXEC DBC.CreateBusinessCalendarPattern
       ('TERADATA', ‘SUNDAY’,’OFF’, ‘Non-work Day’);

    Example  

    The following modifies the ISO business calendar by designating SUNDAY and MONDAY as working days:

    EXEC DBC.CreateBusinessCalendarPattern
       ('ISO', 'SUNDAY','ON', 'Work Day');
     
    EXEC DBC.CreateBusinessCalendarPattern
      ('ISO', 'MONDAY', 'ON', 'Work Day');

    CreateException

    You can use this macro to define an exception list for a specific business calendar. The list identifies all of the days (by date) of the calendar that are exceptions to the pattern defined for the calendar.

    When you execute the macro, the exceptions in the list are inserted into the calendar and into the DBC.BusinessCalendarException table.

    If you try to define an exception for a particular date that is already an exception, an error is returned. To replace the existing exception, you must first delete the existing exception then redefine a new one for the date.

    Required Parameters

     

    Parameter

    Format

    Data Type

    Description

    CalendarName

    VARCHAR(256) CHARACTER SET UNICODE

    NOT CASESPECIFIC

    NOT NULL

    X(128)

    Name of the business calendar. For the system business calendars, it must be Teradata, ISO, or COMPATIBLE.

    ExceptionIndicator

    CHAR(5)

    X(5)

    Type of day for the exception. Must be ON (business day) or OFF (non-business day).

    ExceptionDate

    DATE

    YYYY-MM-DD

    Date of the exception (not the day it was created).

    PatternComment

    VARCHAR(2048)

    X(1024)

    Reason or rationale for the pattern for the calendar day (ON or OFF).

    Usage Notes

    If you try to insert exceptions that are beyond the CalendarPeriod boundaries, the insertion is aborted.

    If you try to insert an exception for a day that is already defined as an exception, you must delete the existing exception for that day and replace it with the new exception.

    Example

    In this example, an OFF and an ON exception are defined for the Teradata business calendar and the information about them is added to the DBC.BusinessCalendarException table. Days with the OFF status are non-business days. Days with the ON status are business days.

    EXEC DBC.CreateException
       ('Teradata', ’OFF’, DATE '2008-03-03', 'Holiday');
     
    EXEC DBC.CreateException
      ('Teradata', ’ON’, DATE '2008-06-07', 'Holiday make up day');
     

    The two exceptions are inserted into the Teradata business calendar. This first exception indicates that March 3, 2008 is now a non-business day (noted in the comment as a holiday). The second exception indicates that June 7, 2008 is now a business day (noted in the comment as a day to make up for a previous holiday).

    The CalendarPeriod of the Teradata calendar is from January 1, 1900 to December 31, 2100. If you try to insert holidays beyond the CalendarPeriod boundary, the insertion is aborted.

    DeleteException

    You can use this macro to delete a particular exception from a specific business calendar. When you delete an exception, the status for that exception day automatically reverts to the status defined for it in the pattern for the calendar.

    When you execute the macro, the exception is removed from the calendar and from the DBC.BusinessCalendarException table.

    If you need to delete all of the exceptions from a calendar, use the DeleteAllExceptions macro (see “DeleteAllExceptions” on page 387).

    Required Parameters

     

    Parameter

    Data Type

    Format

    Description

    CalendarName

    VARCHAR(256) CHARACTER SET UNICODE

    NOT CASESPECIFIC

    NOT NULL

    X(128)

    The name of the business calendar. For the system business calendars, it must be Teradata, ISO, or COMPATIBLE.

    ExceptionDate

    DATE

    YY/MM/DD

    The date of the exception (not the day it was created).

    Example

    In this example, the exception with the date of January 1, 2009 is deleted from the ISO business calendar and from the DBC.BusinessCalendarException table.

    EXEC DBC.DeleteException('ISO', DATE '2009-01-01');

    DeleteAllExceptions

    You can use this macro to delete all of the exceptions from a specific business calendar. When you delete all exceptions, the status for all exception days automatically reverts to the status defined for the days in the pattern for the calendar.

    When you execute the macro, all exceptions are removed from the calendar and from the DBC.BusinessCalendarException table.

    If you need to delete a particular exception from a calendar, use the DeleteException macro (see “DeleteException” on page 387).

    Required Parameter

     

    Parameter

    Data Type

    Format

    Description

    CalendarName

    VARCHAR(128) CHARACTER SET UNICODE

    NOT CASESPECIFIC

    NOT NULL

    X(128)

    The name of the business calendar. For the system business calendars, it must be Teradata, ISO, or COMPATIBLE.

    Example

    In this example, all of the exceptions are removed from the ISO business calendar and from the DBC.BusinessCalendarException table.

    EXEC DBC.DeleteAllExceptions('ISO');