Example: Using the VARDATE Column Data Type - Parallel Transporter

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 (B035-1143).

For complete VARDATE syntax Teradata Parallel Transporter Reference (B035-2436).