15.10 - Using VARDATE Columns To Reformat DateTime Data - Parallel Transporter

Teradata Parallel Transporter User Guide

prodname
Parallel Transporter
vrm_release
15.10
category
User Guide
featnum
B035-2445-035K

Often varying length date, time, timestamp, and interval data is loaded into the Teradata Database as VARCHAR data. The Teradata Database refers to this as DateTime data. For a full definition of DateTime data SQL Data Types and Literals.

In some cases, DateTime data is formatted in a way that does not match the format that the Teradata Database expects. To load this DateTime data, Teradata PT enables you reformat it by specifying in the DEFINE SCHEMA statement of your load job a column type of VARDATE along with input and output format strings. These format strings reformat incoming DateTime data, enabling disparate sources of DateTime data to be loaded into a single Teradata Database.

Columns defined with type VARDATE must be followed by a:

  • Column size
  • FORMATIN string
  • FORMATOUT string.
  • The FORMATIN string specifies the format for the incoming DateTime data.

    The FORMATOUT string specifies the desired output format and must match the format of the database column into which data is being loaded.

    The size of the VARDATE column must always be equal to or greater than the length of the larger formatting string.

    For the DEFINE SCHEMA statement syntax for VARDATE “DEFINE SCHEMA” in the Teradata Parallel Transporter Reference.

    Example : Using the VARDATE Column Data Type

    If the first column of a Teradata Database table is defined with type DATE during table creation, the Teradata Database will only accept incoming Date data during load operations if the data is in a supported format ('YYYY-MM-DD' or 'YY/MM/DD' assuming the system's DateForm setting has not been modified). However, the data we want to load into our example column looks as follows:

    JAN-21 1999 | …
    FEB-03 1997 | …
    AUG-24 2001 | …

    To load this data into our table successfully, we first need to signal to Teradata PT that the first column of input data needs to be reformatted. To do this, we define a Teradata PT SCHEMA object whose first column is of type VARDATE. Then we specify the format of the incoming data and the format of the desired output data via formatting strings. The following schema definition successfully conveys to Teradata PT that the first column of input data needs to be reformatted to match the default DATE format before being sent to the Teradata Database.

    DEFINE SCHEMA VARDATE_EXAMPLE(
    	COL1 VARDATE(15) FORMATIN('MMM-DDBYYYY') FORMATOUT('YY/MM/DD'),
    );

    The following table provides a non-exhaustive set of VARDATE examples with their column definitions, Teradata PT VARDATE formatting strings, and example input and output data. Notice that in some cases Teradata PT attempts to prepend 0s to double digit times and dates that are missing a first digit. This functionality is available only when the numeric value is bounded by separator characters on both sides; otherwise, there can be potential ambiguity in reformatting.

    For more information about the default Date/Time formats that Teradata Database supports, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    For complete VARDATE syntax Teradata Parallel Transporter Reference.

     

    Target Column Type

    Example FORMATIN
    Example Input Data

    Example FORMATOUT
    Example Output Data

    DATE

    MM-DD-YY

    3-25-88

    YY/MM/DD

    88/03/25

    DATE

    E3 DDMMYY

    Wed 110212

    YY/MM/DD

    12/02/11

    DATE

    YYYY-MM-DDBHH:MI:SS

    1970-1-1 12:35:05

    YY/MM/DD

    70/01/01

    DATE FORMAT
    ‘MMM-DD-YY’

    E4BM4/DD/Y4

    Saturday July/4/2009

    MMM-DD-YY

    Jul-04-09

    TIME

    HHMISS

    013251

    HH:MI:SS

    01:32:51

    TIME

    HH:MI T

    11:45 pm

    HH:MI:SS

    11:45:00

    TIME

    ZBHHBMIBSS

    +02:00 7 20 5

    HH:MI:SS

    07:20:05

    TIME FORMAT
    ‘HHBMMBSS’

    YY/MM/DDBHH:MI:SST

    55/08/20 12:35:30AM

    HHBMIBSS

    12 35 30

    TIMESTAMP

    HH:MI:SSBMM/DD/YY

    3:28:59 10/30/99

    YYYY-MM-DDBHH:MI:SS

    1999-10-30 03:28:59

    TIMESTAMP

    MM-DD-Y4 HH:MIT

    4-4-2003 8:3OPM

    YYYY-MM-DDBHH:MI:SS

    2003-04-04 08:20:00

    TIMESTAMP(6)

    M4BDDBY4

    January 20 1990

    YYYY-MM-DDBHH:MI:SS.S(6)

    1990-01-20 00:00:00.000000

    TIMESTAMP(2)

    DD/MMM/YYBHH:MI:SS.S(6)Z

    21/Dec/12 3:45:30.143257+02:00

    YYYY-MM-DDBHH:MI:SS.S(2)

    2012-12-21 03:45:30.14

    INTERVAL YEAR(2)
    TO MONTH

    HH:MIT MM-DD-YY

    12:30AM 9-20-96

    YY-MM

    96-09

    INTERVAL DAY(2)
    TO HOUR

    DDMMYYYYHHMISS

    04052014101830

    DDBHH

    04 10