Using VARDATE Columns To Reformat DateTime Data - Parallel Transporter

Teradata® Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
17.00
Published
August 31, 2020
Language
English (United States)
Last Update
2020-08-27
dita:mapPath
zae1544831938751.ditamap
dita:ditavalPath
tvt1507315030722.ditaval
dita:id
B035-2445
lifecycle
previous
Product Category
Teradata Tools and Utilities

Often varying length date, time, timestamp, and interval data is loaded into the database as VARCHAR data. The database refers to this as DateTime data. For a full definition of DateTime data, see Teradata Vantage™ - Data Types and Literals, B035-1143.

In some cases, DateTime data is formatted in a way that does not match the format that the 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 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.