15.00 - About the Business Calendar Views - 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 the Business Calendar Views

You can query business calendar views to retrieve calendar information relevant to your business, such as the first business day of a week.

Sys_Calendar.BusinessCalendarExceptions

This view provides information about exceptions defined for a calendar, such as a holiday on Monday, which is normally a working day. You can query the view to check the exceptions.

 

View Column Name

Description

Data Type

Format

CalendarName

The name of the calendar.

VARCHAR(128)

X(128)

ExceptionIndicator

Returns ON for a working day and OFF for a nonworking day.

VARCHAR(3)

X(3)

ExceptionDate

The exception date set for the calendar.

DATE

YY/MM/DD

ExceptionReason

The reason for the exception.

VARCHAR(1024)

X(1024)

CreatorName

The user who created the exception.

VARCHAR(128)

X(128)

CreationTime

The timestamp of exception creation.

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

Sys_Calendar.BusinessCalendarPatterns

This view provides information about patterns defined for a calendar, such as a weekday/weekend pattern.

 

View Column Name

Description

Data Type

Format

CalendarName

The name of the calendar.

VARCHAR(128)

X(128)

DayName

The day name, for example, Friday

VARCHAR(9)

X(9)

Pattern

The ON or OFF pattern settings.

VARCHAR(3)

X(3)

PatternComment

The comment for each day in the pattern.

VARCHAR(1024)

X(1024)

CreatorName

The user who created the pattern.

VARCHAR(128)

X(128)

LastModified

The timestamp of last pattern change.

TIMESTAMP(0)

YYYY-MM-DD:HH:MI:SS

Sys_Calendar.BusinessCalendar

This Sys_Calendar view provides business functionality for the three system-defined business calendars.

 

View Column Name

Description

Data Type

Format

Calendar_Date

The date.

DATE

YY/MM/DD

Day_of_Week

An integer value that ranges from 1 to 7.

INTEGER

-(10)9

Day_of_Month

A integer value that ranges from 1 to 31.

INTEGER

-(10)9

Day_of_Year

An integer value that ranges from 1 to 366.

INTEGER

-(10)9

Day_of_Calendar

The number of days since the beginning of the calendar.

INTEGER

-(10)9

Weekday_of_Month

The nth occurrence of the weekday in the month (1-5).

INTEGER

-(10)9

Week_of_Month

The week number of the month, ranging from 0 to 5.

INTEGER

-(10)9

Week_of_Quarter

The week number of the quarter, ranging from 0 to 14.

INTEGER

-(10)9

Week_of_Year

The week number of the year, ranging from 0 to 53.

INTEGER

-(10)9

Week_of_Calendar

For a given date, the number of the week in the calendar in which it occurs.

INTEGER

-(10)9

Month_of_Quarter

For a given date, the number of the month in the quarter in which it occurs.

INTEGER

-(10)9

Month_of_Year

For a given date, the number of the month in the year in which it occurs.

INTEGER

-(10)9

Month_of_Calendar

For a given date, the number of the month in the calendar in which it occurs.

INTEGER

-(10)9

Quarter_of_Year

For a given date, the quarter of the year in which it occurs.

INTEGER

-(10)9

Quarter_of_Calendar

For a given date, the quarter number of the calendar in which it occurs.

INTEGER

-(10)9

Year_of_Calendar

For a given date, the year number of the calendar in which it occurs.

INTEGER

-(10)9

WeekEnd

The end of the week for the given date.

DATE

YY/MM/DD

WeekBegin

The beginning of the week for the given date.

DATE

YY/MM/DD

MonthBegin

The beginning of the month for the given date.

DATE

YY/MM/DD

MonthEnd

The end of the month for the given date.

DATE

YY/MM/DD

QuarterBegin

The beginning of the quarter for the given date.

DATE

YY/MM/DD

QuarterEnd

The end of the quarter for the given date.

DATE

YY/MM/DD

YearBegin

The beginning of the year for the given date.

DATE

YY/MM/DD

YearEnd

The end of the year for the given date.

DATE

YY/MM/DD

IsBusinessDay

Whether or not the given day is a business day.

BYNET

-(3)9

BusinessWeekBegin

The first working day of the week in which the given date occurs.

DATE

YY/MM/DD

BusinessWeekEnd

The last working day of the week in which the given date occurs.

DATE

YY/MM/DD

BusinessMonthBegin

The first working day of the month in which the given date occurs.

DATE

YY/MM/DD

BusinessMonthEnd

The last working day of the month in which the given date occurs.

DATE

YY/MM/DD

BusinessQuarterBegin

The first working day of the quarter in which the given date occurs.

DATE

YY/MM/DD

BusinessQuarterEnd

The last working day of the quarter in which the given date occurs.

DATE

YY/MM/DD

BusinessYearBegin

The first working day of the year in which the given date occurs.

DATE

YY/MM/DD

BusinessYearEnd

The last working day of the year in which the given date occurs.

DATE

YY/MM/DD

Example  

This query returns the day of the week using the ISO calendar:

Set session calendar = iso;
Sel day_of_week from Sys_Calendar.Calendar where calendar_date = date '2011-01-01';
 
day_of_week
-----------
6

Example  

This query returns the day of the week using the COMPATIBLE calendar:

Set session calendar = compatible;
 
Sel day_of_week from Sys_Calendar.Calendar where calendar_date = date '2011-01-01';
 
day_of_week
-----------
1

Example  

This query returns the beginning of the week using the ISO calendar:

Set session calendar = iso;
 
Sel weekBegin from Sys_Calendar.BusinessCalendar where calendar_date = date '2011-01-01';
 
WeekBegin
-----------
10/12/27

Example  

This query returns the beginning of the week using the COMPATIBLE calendar:

Set session calendar = compatible;
Sel weekBegin from Sys_Calendar.BusinessCalendar where calendar_date = date '2011-01-01';
WeekBegin
-----------
11/01/01

Example  

By default, Monday is a working day in the ISO calendar. The following query adds an OFF exception for January 1, 2007, which is a Monday.

Exec DBC.CreateException('ISO', 'OFF', date '2007-01-01',  'Newyear Day');

The following query returns the next working day after January 1, 2007:

Sel BusinessWeekBegin from Sys_Calendar.BusinessCalendar where calendar_date = date '2007-01-01';
BusinessWeekBegin
-----------
07/01/02 

Example  

This query returns the beginning of the week and the week number for 2009, assuming that no calendar is set for the session. The session uses the default business calendar “Teradata.”

SEL WeekBegin, week_of_year FROM Sys_Calendar.BusinessCalendar WHERE calendar_date = DATE '2009-03-15'; 
Week_begin         week_of_year
-----------        -------------------
09/03/15           11