16.20 - Using VARDATE Columns To Reformat DateTime Data - 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

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.