15.10 - ANSI/SQL DateTime Specifications - Parallel Transporter

Teradata Parallel Transporter Application Programming Interface

prodname
Parallel Transporter
vrm_release
15.10
category
Programming Reference
featnum
B035-2516-035K

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.

 

Table 33: 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:
Format (
n = 0):
Example:
Format: (n = 4):
Example:

CHAR(8 + n + (1 if n > 0, otherwise 0))
hh:mm:ss
11:37:58
hh:mm:ss.ssss
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:
Format (
n = 0):
Example:
Format (n = 4):
Example:

CHAR(19 + n + (1 if n > 0, otherwise 0))
yyyy-mm-dd hh:mm:ss
1998-09-04 11:37:58
yyyy-mm-dd hh:mm:ss.ssss
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:
Format (
n = 0):
Example:
Format (n = 4):
Example:

CHAR(14 + n + (1 if n > 0, otherwise 0))
hh:mm:ss{±}hh:mm
11:37:58-08:00
hh:mm:ss.ssss {±} hh:mm
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:
Format (
n = 0):
Example
Format (n = 4):
Example:

CHAR(25 + n + (1 if n > 0, otherwise 0))
yyyy-mm-dd hh:mm:ss{±}hh:mm
1998-09-24 11:37:58+07:00
yyyy-mm-dd hh:mm:ss.ssss{±}hh:mm
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:
Format (
n = 2):
Example:
Format (n = 4):
Example:

CHAR(n+1)
yy
98
yyyy
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:
Format (
n = 2):
Example:
Format (n = 4):
Example:

CHAR(n + 3)
yy-mm
98-12
yyyy-mm
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:
Format (
n = 2):
Example:
Format (n = 4):
Example:

CHAR(n)
mm
12
mmmm
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:
Format (
n = 2):
Example:
Format (n = 4):
Example:

CHAR(n)
dd
31
dddd
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:
Format (
n = 2):
Example:
Format (n = 4):
Example:

CHAR(n + 3)
dd hh
31 12
dddd hh
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:
Format (
n = 2):
Example:
Format (n = 4):
Example:

CHAR(n + 6)
dd hh:mm
31 12:59
dddd hh:mm
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:
    Format (
    n = 2, m = 0):
    Example:
    Format (n = 4, m = 4):
    Example:

    CHAR(n + 9 + m + (1 if m > 0, 0 otherwise))
    dd hh:mm:ss
    31 12:59:59
    dddd hh:mm:ss.ssss
    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:
    Format (
    n = 2):
    Example:
    Format (n = 4):
    Example:

    CHAR(n)
    hh
    12
    hhhh
    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:
    Format (
    n = 2):
    Example:
    Format (n = 4):
    Example:

    CHAR(n + 3)
    hh:mm
    12:59
    hhhh:mm
    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:
    Format (
    n = 2, m = 0):
    Example:
    Format (n = 4, m = 4):
    Example:

    CHAR(n + 6 + m + (1 if m > 0, 0 otherwise))
    hh:mm:ss
    12:59:59
    hhhh:mm:ss.ssss
    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:
    Format (
    n = 2):
    Example:
    Format (n = 4):
    Example:

    CHAR(n)
    mm
    59
    mmmm
    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:
    Format (
    n = 2, m = 0):
    Example:
    Format (n = 4, m = 4):
    Example:

    CHAR(n + 3 + m + (1 if m > 0, 0 otherwise))
    mm:ss
    59:59
    mmmm:ss.ssss
    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:
    Format (
    n = 2, m = 0):
    Example:
    Format (n = 4, m = 4):
    Example:

    CHAR(n + m + (1 if m > 0, 0 otherwise))
    ss
    59
    ssss.ssss
    0059.1234