problem with import

Database
Enthusiast

problem with import

i am having a table A
CREATE SET TABLE A,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
x DATE FORMAT 'YYYY-MM-DD',
)
PRIMARY INDEX (x);

following is my data in textpad
07/07/08

i am using bteq import to insert this in table A
with script

.import REPORT file='C:\importtest2.txt'
.quiet on
.repeat 1
using
x (char(8))
values
(

cast(:x as date)

);

but i am getting an error saying
invalid date supplied for A.x
please clarify this
what i am doing wrong also please through a light on why this error is handling i.e how date format is working internally here

1 REPLY
Teradata Employee

Re: problem with import

Your CAST does not specify a FORMAT, so Teradata will try to use 'YYYY-MM-DD' (which should always be the default for ANSIDATE, and in this case is also the column default that INTEGERDATE would use).

So the first part of your answer is to specify an explicit format, e.g.
CAST(:X AS DATE FORMAT 'MM/DD/YY')
But unless you have set nonzero Century Break in DBSControl, two-digit years are assumed to be 19yy. So you may need to a apply an adjustment, e.g.
+INTERVAL '100' YEAR
and it may need to be done conditionally in a CASE statement, e.g.

CASE WHEN CAST(:X AS DATE FORMAT 'MM/DD/YY') (NAMED Z) >= DATE'1950-01-01'
THEN Z
ELSE Z+INTERVAL '100' YEAR
END