Prerequisites for Using the Business Calendar Functions - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantage™

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