- 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(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.
The following rules and restrictions apply to specifying a simple expression as a time zone value.