ANSI/SQL DateTime Specifications
In Teradata PT, the TIME, TIMESTAMP and INTERVAL data types are not part of the syntax as they are in SQL. No Teradata PT data type exists for TIME or TIMESTAMP. Therefore, to load or export data, manually convert the desired data type to the ANSI/SQL DateTime data types by specifying the appropriate fixed CHAR column in the schema as specified in Table 33.
Specification |
|
TIME TIME (n) |
|
Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.) |
|
Convert to: |
CHAR(8 + n + (1 if n > 0, otherwise 0)) |
TIMESTAMP TIMESTAMP (n) |
|
Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.) |
|
Convert to: |
CHAR(19 + n + (1 if n > 0, otherwise 0)) |
TIME WITH TIME ZONE TIME (n) WITH TIME ZONE |
|
Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.) |
|
Convert to: |
CHAR(14 + n + (1 if n > 0, otherwise 0)) |
TIMESTAMP WITH TIME ZONE TIMESTAMP (n) WITH TIME ZONE |
|
Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.) |
|
Convert to: |
CHAR(25 + n + (1 if n > 0, otherwise 0)) |
INTERVAL YEAR INTERVAL YEAR (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n+1) |
INTERVAL YEAR TO MONTH INTERVAL YEAR (n) TO MONTH |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n + 3) |
INTERVAL MONTH INTERVAL MONTH (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n) |
INTERVAL DAY INTERVAL DAY (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n) |
INTERVAL DAY TO HOUR INTERVAL DAY (n) TO HOUR |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n + 3) |
INTERVAL DAY TO MINUTE INTERVAL DAY (n) TO MINUTE |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n + 6) |
INTERVAL DAY TO SECOND INTERVAL DAY (n) TO SECOND INTERVAL DAY TO SECOND (m) INTERVAL DAY (n) TO SECOND (m) |
|
Where: Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n + 9 + m + (1 if m > 0, 0 otherwise)) |
INTERVAL HOUR INTERVAL HOUR (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n) |
INTERVAL HOUR TO MINUTE INTERVAL HOUR (n) TO MINUTE |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n + 3) |
INTERVAL HOUR TO SECOND INTERVAL HOUR (n) TO SECOND INTERVAL HOUR TO SECOND (m) INTERVAL HOUR (n) TO SECOND (m) |
|
Where: Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n + 6 + m + (1 if m > 0, 0 otherwise)) |
INTERVAL MINUTE INTERVAL MINUTE (n) |
|
Where n is the number of digits, 1 through 4. (Default = 2.) Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n) |
INTERVAL MINUTE TO SECOND INTERVAL MINUTE (n) TO SECOND INTERVAL MINUTE TO SECOND (m) INTERVAL MINUTE (n) TO SECOND (m) |
|
Where: Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n + 3 + m + (1 if m > 0, 0 otherwise)) |
INTERVAL SECOND INTERVAL SECOND (n) INTERVAL SECOND (n, m) |
|
Where: Results include one leading blank space (for positive values) or a minus sign (for negative values). |
|
Convert to: |
CHAR(n + m + (1 if m > 0, 0 otherwise)) |