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

Teradata® Parallel Transporter User Guide

Parallel Transporter
Release Number
August 2020
English (United States)
Last Update

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

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 to 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, see “DEFINE SCHEMA” in Teradata® Parallel Transporter Reference, B035-2436.