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 the following table.
| 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)) Format (n = 0): hh:mm:ss Example: 11:37:58 Format: (n = 4): hh:mm:ss.ssss Example: 11:37:58.1234 |
| 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)) Format (n = 0): yyyy-mm-dd hh:mm:ss Example: 1998-09-04 11:37:58 Format (n = 4): yyyy-mm-dd hh:mm:ss.ssss Example: 1998-09-04 11:37:58.1234 |
| 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)) Format (n = 0): hh:mm:ss{±}hh:mm Example: 11:37:58-08:00 Format (n = 4): hh:mm:ss.ssss{±}hh:mm Example: 11:37:58.1234-08:00 |
| 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)) Format (n = 0): yyyy-mm-dd hh:mm:ss{±}hh:mm Example: 1998-09-24 11:37:58+07:00 Format (n = 4): yyyy-mm-dd hh:mm:ss.ssss{±}hh:mm Example: 1998-09-24 11:37:58.1234+07:00 |
| 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) Format (n = 2): yy Example: 98 Format (n = 4): yyyy Example: 1998 |
| 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) Format (n = 2): yy-mm Example: 98-12 Format (n = 4): yyyy-mm Example: 1998-12 |
| 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) Format (n = 2): mm Example: 12 Format (n = 4): mmmm Example: 0012 |
| 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) Format (n = 2): dd Example: 31 Format (n = 4): dddd Example: 0031 |
| 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) Format (n = 2): dd hh Example: 31 12 Format (n = 4): dddd hh Example: 0031 12 |
| 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) Format (n = 2): dd hh:mm Example: 31 12:59 Format (n = 4): dddd hh:mm Example: 0031 12:59 |
| 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)) Format (n = 2, m = 0): dd hh:mm:ss Example: 31 12:59:59 Format (n = 4, m = 4): dddd hh:mm:ss.ssss Example: 0031 12:59:59:59.1234 |
| 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) Format (n = 2): hh Example: 12 Format (n = 4): hhhh Example: 0012 |
| 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) Format (n = 2): hh:mm Example: 12:59 Format (n = 4): hhhh:mm Example: 0012:59 |
| 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)) Format (n = 2, m = 0): hh:mm:ss Example: 12:59:59 Format (n = 4, m = 4): hhhh:mm:ss.ssss Example: 0012:59:59.1234 |
| 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) Format (n = 2): mm Example: 59 Format (n = 4): mmmm Example: 0059 |
| 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)) Format (n = 2, m = 0): mm:ss Example: 59:59 Format (n = 4, m = 4): mmmm:ss.ssss Example: 0059:59.1234 |
| 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)) Format (n = 2, m = 0): ss Example: 59 Format (n = 4, m = 4): ssss.ssss Example: 0059.1234 |