17.00 - ANSI/SQL DateTime Specifications - Parallel Transporter

Teradata® Parallel Transporter Application Programming Interface Programmer Guide

prodname
Parallel Transporter
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-2516-220K

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.

ANSI/SQL DateTime Specifications  
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:
  • n is the number of digits, 1 through 4. (Default = 2.)
  • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)

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:
  • n is the number of digits, 1 through 4. (Default = 2.)
  • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)

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:
  • n is the number of digits, 1 through 4. (Default = 2.)
  • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)

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:
  • n is the number of digits, 1 through 4. (Default = 2.)
  • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)

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