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