A date_time_primary can include an AT LOCAL or AT [TIME ZONE] clause only if the date_time_primary evaluates to a TIME or TIMESTAMP value or is the built-in function CURRENT_DATE or DATE.
The effect is to adjust date_time_term to be in accordance with the specified time zone displacement.
The expression that specifies the time zone displacement in an AT [TIME ZONE] clause is implicitly converted, 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.
| Data type of expression | Implicit Conversion |
|---|---|
| INTERVAL HOUR(n) TO MINUTE where n is not 2 |
CAST(expression AS INTERVAL HOUR(2) TO MINUTE) |
|
CAST(expression AS INTERVAL HOUR(2) TO MINUTE) |
|
CAST(CAST(expression AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE) |
| DECIMAL/NUMERIC if the fractional precision is greater than 0 | CAST(CAST((expression)*60 AS INTERVAL MINUTE(4)) AS INTERVAL HOUR(2) TO MINUTE) |
| Character with CHARACTER SET UNICODE | CAST(CAST(expression AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE) If an error occurs for the above CAST statement, Teradata Database attempts the following: CAST(expression AS INTERVAL HOUR(2) TO MINUTE) If an error occurs for this CAST statement also, Teradata Database treats the character value as a time zone string. |
| Character that is not CHARACTER SET UNICODE | TRANSLATE(expression USING source_repertoire_name_TO_Unicode) where source_repertoire_name is the server character set of expression. The translated value is then processed as above for a character value with CHARACTER SET UNICODE. |
| other | An error is returned. |
If the conversion to INTERVAL HOUR(2) TO MINUTE results in a value that is not between INTERVAL -'12:59' HOUR TO MINUTE and INTERVAL '14:00' HOUR TO MINUTE, an error is returned.
- Time zone strings that do not follow separate daylight savings time (DST) and standard time zone displacements from Coordinated Universal Time (UTC) time.
- Time zone strings that follow different DST and standard time zone displacements from UTC time.
The following time zone strings are supported.
| Strings that do not follow separate DST and standard time zone displacements | ||
|---|---|---|
|
|
|
| Strings that follow different DST and standard time zone displacements | ||
|---|---|---|
|
|
|
Teradata Database resolves the time zone string and calculates the time zone displacement for the session or requested query.
Teradata Database resolves the time zone string based on the rules and time zone displacement information stored in the system UDF (user-defined function), GetTimeZoneDisplacement.
If the time zone strings provided by Teradata do not meet your requirements, you may add new time zone strings or modify the existing time zone strings by modifying or adding new rules to the GetTimeZoneDisplacement UDF.
- With the following built-in functions:
- “CURRENT_DATE”.
- “CURRENT_TIME”.
- “CURRENT_TIMESTAMP”.
- “DATE”.
- “TIME”.If you specify these built-in functions with an AT LOCAL clause, the value returned depends on the setting of the DBS Control flag TimeDateWZControl.
- When converting DateTime data types using the CAST function or Teradata conversion syntax. You can specify the time zone used for the CAST or conversion as the source time zone, a specific time zone displacement or time zone string, or the current session time zone.
- With the EXTRACT function to specify a time zone for the source expression before extracting the fields.