17.10 - Prerequisites for Using the Business Calendar Functions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)

Before you can use these functions, you must run the Database Initialization Program (DIP) utility and execute the DIPSYSFNC script. The DIPALL or DIPSYSFNC script will create the calendar functions in the TD_SYSFNLIB database. For more information about the DIP utility, see Teradata Vantage™ - Database Utilities, B035-1102.

If you have a UDF with the same name as a business calendar function, you must remove that UDF from the normal UDF search path before you can invoke the business calendar function. If the business calendar function is not found in the current database, Vantage searches for the function in the TD_SYSFNLIB database. Alternatively, you may invoke the calendar function by using the fully qualified syntax, TD_SYSFNLIB.calendar_function_name.

Example: Cumulative Sales During First Week of All Quarters

This example finds cumulative sales of an item during first week of all quarters. Results are shown for each calendar setting.

SEL Item_Code, SUM(Sale_Amt) FROM Sales_Tbl
WHERE WeekNumber_Of_Quarter(Sale_Date) = 1
GROUP BY Item_Code;

SET SESSION calendar = Teradata;

Item_Code SUM(Sale_Amt)
101 225
102 120

SET SESSION calendar = Compatible

Item_Code SUM(Sale_Amt)
101 245
102 100

SET SESSION calendar = ISO;

Item_Code SUM(Sale_Amt)
101 135
102 30
Sales_Tbl
Item_Code Sale_Amt Sale_Date
101 10 2008-12-30
101 15 2008-12-31
101 20 2009-01-01
101 25 2009-01-02
101 30 2009-01-03
101 35 2009-01-04
101 40 2009-01-05
101 45 2009-01-06
101 50 2009-01-07
101 55 2009-01-08
102 10 2009-01-01
102 20 2009-01-03
102 30 2009-01-05
102 40 2009-01-07
102 50 2009-01-09
Jan 2009 Calendar
S M T W T F S
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31