Usability Features|VARDATE - Usability Features - Parallel Transporter

Teradata® Parallel Transporter User Guide - 17.20

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Parallel Transporter
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-08-25
dita:mapPath
uzp1645128359760.ditamap
dita:ditavalPath
tvt1507315030722.ditaval
dita:id
B035-2445
Product Category
Teradata Tools and Utilities

In some cases, the VARDATE formatting feature will accept DateTime data that does not explicitly match the FORMATIN string supplied. These cases are rare, but have been implemented to extend the usability of the VARDATE formatting feature.

When the two-digit month, day, hour, second, or minute formatting characters are supplied in the FORMATIN string and the matching value in the underlying data is a single digit, a zero will be prepended to the single-digit values before the data is converted to the format specified in the FORMATOUT string. When the full day name formatting characters are supplied in the FORMATIN string and the matching value in the underlying data is the abbreviated day name, the abbreviated day name will be accepted. Similarly, when the abbreviated day name formatting characters are supplied in the FORMATIN string and the matching value in the underlying data is the full day name, the full day name will be accepted.

VARDATE Conversions

The VARDATE conversions can be flexible in the following formatting characters specified in FORMATIN when it comes to missing data at the end of the input string supplied for FORMATIN, and therefore defaulted:

Format String Description Results if corresponding data is missing
YY Two-digit year "00" will be processed resulting in 2000
HH Two-digit hour "00" will be processed
MI Two-digit minute "00" will be processed
SS Two-digit second "00" will be processed
SS.S (N) Two-digit second followed by fractional seconds "00.000000" will be processed
T Use 12-hour format "am" will be processed
Z Time zone "+00.00" will be processed

VARDATE data missing from the beginning or middle of the input string should still result in an error since it can be unclear which part of the input string is actually missing. And, if a VARDATE input string does not exist at all, then the data is not considered missing. It is considered a NULL column (such as, the last row in the following example.)

For example, consider the following schema definition:

DEFINE SCHEMA PRODUCT_TABLE_SCHEMA
DESCRIPTION 'PRODUCT INFORMATION SCHEMA'
(
COL1 VARCHAR(10),
COL2 VARDATE(30) FORMATIN('YYYY/MMM/DDBHH/MI/SS.S(6)') FORMATOUT('YYYY-MM-DDBHH:MI:SS.S(6)'),
COL3 VARCHAR(10)
);

Now consider the following delimited data:

aaa,2001/Jan/01 01/01/01.123456,AAA
bbb,2002/Feb/02 02/02/02,BBB
ccc,2003/Mar/03 03/03/,CCC
ddd,2004/Apr/04 04/04,DDD
eee,2005/May/05 05/,EEE
fff,2006/Jun/06 06,FFF
ggg,2007/Jul/07 ,GGG
hhh,2008/Aug/08,HHH
iii,,III

The insertion of this data into a table should look similar to the following:

COL001     COL002                     COL003
---------- -------------------------- ----------
aaa        2001-01-01 01:01:01.123456 AAA
bbb        2002-02-02 02:02:02.000000 BBB
ccc        2003-03-03 03:03:00.000000 CCC
ddd        2004-04-04 04:04:00.000000 DDD
eee        2005-05-05 05:00:00.000000 EEE
fff        2006-06-06 06:00:00.000000 FFF
ggg        2007-07-07 00:00:00.000000 GGG
hhh        2008-08-08 00:00:00.000000 HHH
iii        ?                          III