Calling Overloaded Functions with Period Type Arguments - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

When you call an overloaded function that has a PERIOD(TIME(n)), PERIOD(TIME(n) WITH TIME ZONE), PERIOD(TIMESTAMP(n)), or PERIOD(TIMESTAMP(n) WITH TIME ZONE) parameter type (where n represents precision), and you pass a period argument specifying a particular precision, Teradata Database uses these steps to determine which function to invoke:

  1. If one of the functions has parameter types and precision that are identical to the corresponding argument types and precision, then select that function.
  2. If an implicit cast of the argument (source) to parameter (target) type of the function is possible and more than one function qualifies, then select the function in order of precedence listed below. For period data types, implicit cast is supported for these cases:
    Source Target TD 15.10 - CMS conversion,
    CHAR or VARCHAR PERIOD(DATE)
    CHAR or VARCHAR PERIOD(TIME[(n)])
    CHAR or VARCHAR PERIOD(TIME[(n)] WITH TIME ZONE)
    CHAR or VARCHAR PERIOD(TIMESTAMP[(n)])
    CHAR or VARCHAR PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)
    PERIOD(DATE) CHAR or VARCHAR
    PERIOD(TIME[(n)]) CHAR or VARCHAR
    PERIOD(TIME[(n)] WITH TIME ZONE) CHAR or VARCHAR
    PERIOD(TIMESTAMP[(n)]) CHAR or VARCHAR
    PERIOD(TIMESTAMP[(n)] WITH TIME ZONE) CHAR or VARCHAR
    PERIOD(TIMESTAMP[(n)]) PERIOD(TIMESTAMP[(n)])
    PERIOD(TIMESTAMP[(n)]) PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)
    PERIOD(TIMESTAMP[(n)] WITH TIME ZONE) PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)
    PERIOD(TIMESTAMP[(n)] WITH TIME ZONE) PERIOD(TIMESTAMP[(n)])
    PERIOD(TIME[(n)]) PERIOD(TIME[(n)])
    PERIOD(TIME[(n)]) PERIOD(TIME[(n)] WITH TIME ZONE)
    PERIOD(TIME[(n)] WITH TIME ZONE) PERIOD(TIME[(n)] WITH TIME ZONE)
    PERIOD(TIME[(n)] WITH TIME ZONE) PERIOD(TIME[(n)])

    If both Source and Target are period data type, then based on the implicit conversion allowed between period types, the order of precedence from high to low is:

    Source Target
    PERIOD(TIME[(n)]) PERIOD(TIME[(n)])

    PERIOD(TIME[(n)] WITH TIME ZONE)

    PERIOD(TIME[(n)] WITH TIME ZONE) PERIOD(TIME[(n)] WITH TIME ZONE)

    PERIOD(TIME[(n)])

    PERIOD(TIMESTAMP[(n)]) PERIOD(TIMESTAMP[(n)])

    PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)

    PERIOD(TIMESTAMP[(n)] WITH TIME ZONE) PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)

    PERIOD(TIMESTAMP[(n)])

    If the source is CHAR or VARCHAR and the target is a period type, the order of precedence from high to low is:

    Source Target
    CHAR or VARCHAR PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)

    PERIOD(TIMESTAMP[(n)])

    PERIOD(DATE)

    PERIOD(TIME[(n)] WITH TIME ZONE)

    PERIOD(TIME[(n)])

    If the source is a period data type and the target is CHAR or VARCHAR, the order of precedence from high to low is:

    Source Target
    Any period data type CHAR

    VARCHAR

If you want to override these precedence rules, you must explicitly CAST the argument to the required parameter type.