Using VARDATE Columns To Reformat DateTime Data - Parallel Transporter

Teradata Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-05-09
dita:mapPath
vyv1488824663502.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.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 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 (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 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 “DEFINE SCHEMA” in the Teradata Parallel Transporter Reference (B035-2436).