15.00 - Rules and Restrictions for Specifying Simple Expressions for Time Zones - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Rules and Restrictions for Specifying Simple Expressions for Time Zones

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, Teradata Database 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 Teradata Database …

    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” on page 889 for a comprehensive list of valid time zone strings.

  • Teradata Database 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, Teradata Database 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

    Teradata Database 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.