17.10 - Rules and Restrictions for Specifying Simple Expressions for Time Zones - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
The following rules and restrictions apply to specifying a simple expression as a time zone value.
  • The simple expression you specify must be a constant; otherwise, the system aborts the request and returns an error to the requestor.
  • The current session time zone can be either a time zone displacement or time zone string.
    IF the current session time zone is … THEN Vantage
    a time zone displacement uses that time zone displacement.
    a time zone string determines the time zone displacement based on the specified time zone string and the UTC TIMESTAMP value for the source expression.

    See Daylight Saving Time and Time Zone Strings Specified As Time Zone Strings for a comprehensive list of valid time zone strings.

  • Vantage implicitly converts the simple expression you specify as needed and if allowed, to a time zone displacement or time zone string depending on its data type as defined in the following table.
    This expression data type … Is implicitly converted as follows …
    • BIGINT
    • BYTEINT
    • INTEGER
    • SMALLINT
    CAST(CAST (expression AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE
    • DECIMAL
    • NUMERIC

    where scale = 0

    CAST(CAST (expression AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE
    • DECIMAL
    • NUMERIC

    where scale > 0

    CAST(CAST((expression)*60 AS INTERVAL MINUTE(4)) AS INTERVAL HOUR(2) TO MINUTE)
    INTERVAL HOUR(2) TO MINUTE no conversion
    INTERVAL HOUR(n) TO MINUTE, where n ¼ 2 CAST(expression) AS INTERVAL HOUR(2) TO MINUTE)
    • INTERVAL HOUR
    • INTERVAL DAY

    If the INTERVAL DAY value you specify is anything other than INTERVAL '0' DAY or INTERVAL -'0' DAY, it eventually fails because it becomes too large to be a time zone displacement.

    • INTERVAL DAY TO HOUR
    • INTERVAL DAY TO MINUTE
    • INTERVAL DAY TO SECOND
    • INTERVAL HOUR
    • INTERVAL HOUR TO SECOND
    • INTERVAL MINUTE
    • INTERVAL MINUTE TO SECOND
    • INTERVAL SECOND
    CAST(expression) AS INTERVAL HOUR(2) TO MINUTE)
    CHARACTER with CHARACTER SET UNICODE Attempt to CAST(CAST(se AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE).

    If an error occurs for the casts, attempt to CAST(expression AS INTERVAL HOUR(2) TO MINUTE).

    If still unable to cast expression, Vantage treats the character value as a time zone string.

    CHARACTER with CHARACTER SET ¼ UNICODE TRANSLATE(expression USING source_repertoire_name_TO_Unicode)

    where source_repertoire_name is the server character set of expression.

    If an error occurs with the translated value, it is then handled as in the previous row for a character value with CHARACTER SET UNICODE replacing expression with the translation of expression.

    anything else The system aborts the request and returns an error to the requestor.
  • Specifying a simple expression without first specifying TIME ZONE is valid syntax and has the same effect as specifying TIME ZONE simple_expression.