I getting a records from text file and needs to be loaded into teradata. One of the filed is going to be date. The value i am getting from text file is like this '20091102'. How do I load this into teradata as date field. It should be loaded like '2009/11/02'.?
Thanks for your reply. I still have issues. I created table like this. CREATE SET TABLE hccloh_ushare.test_date ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( Tst_date DATE FORMAT 'YYYYMMDD') PRIMARY INDEX ( Tst_date );
Inserted like this. Insert into hccloh_ushare.test_date values('20091101') Getting error :3535 A charcter string failed convertion to numeric value.
Tried like this. Insert into hccloh_ushare.test_date values(20091101)
Then the result is 3909/11/02 which is not correct.
even I also tried but I have not got any error. May be you check the session properties by HELP SESSION. Check the transaction semantics. Otherwise I do not see any error. can you paste the output for HELP SESSION ?
please change the default dateform to integer date SET SESSION DATEFORM = INTEGERDATE; and then run the querries. At present your settings are in ANSIDATE format That's why the problem is. if you can not then you have to change the value as below while inserting you have to specify in 'yyyy-mm-dd' format and then insert. create volatile table test12 ( load_date date format 'yyyymmdd' ) on commit preserve rows ;