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
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