16.20 - Example: Using the VARDATE Column Data Type - Parallel Transporter

Teradata® Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-27
dita:mapPath
uah1527114222342.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval

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).

Target Column Type Example FORMATIN Example FORMATOUT
Example Input Data 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