All Forums Database
prakhar 101 posts Joined 05/08
11 Nov 2008
problem with import

i am having a table ACREATE 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 textpad07/07/08i am using bteq import to insert this in table Awith script.import REPORT file='C:\importtest2.txt'.quiet on.repeat 1usingx (char(8))values(cast(:x as date));but i am getting an error saying invalid date supplied for A.xplease 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

Fred 1096 posts Joined 08/04
11 Nov 2008

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' YEARand 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' YEAREND

You must sign in to leave a comment.