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:
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 FORMATOUT |
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 |
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 |
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) |
HH:MIT MM-DD-YY 12:30AM 9-20-96 |
YY-MM 96-09 |
INTERVAL DAY(2) |
DDMMYYYYHHMISS 04052014101830 |
DDBHH 04 10 |