15.00 - About Business Calendar Macros - Teradata Database

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');