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 |