|
|
|
Junior Member
 
Group: Forum Members
Last Login: 11/18/2009 10:51:34 AM
Posts: 10,
Visits: 36
|
|
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'.?
eg;
What I get
date
-----
20091102
I Should load this to teradata as date value.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/19/2009 9:26:30 PM
Posts: 6,
Visits: 11
|
|
you can define the column as date format 'yyyymmdd'.
Display will depend on the setting in Queryman.
go to tools--> options-->Genral
and choose the way you want to disply.
for reference you can use below.It worked for me
create volatile table test12
( load_date date format 'yyyymmdd')
on commit preserve rows ;
insert into test12 values ('20091102')
depending on the setting the display will change.
sel * from test12;
11/02/2009
2009-11-02
Rupesh Baheti
Datawarehouse Analyst
OCBC Bank
Singapore
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/18/2009 10:51:34 AM
Posts: 10,
Visits: 36
|
|
Hi,
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.
Please advise.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 11/19/2009 7:45:03 AM
Posts: 39,
Visits: 169
|
|
I have tried to run the same SQL
Insert into hccloh_ushare.test_date values('20091101')
through both SQL Assitent and BTEQ and it is running fine without any error and it inserts date 11/01/2009
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/19/2009 9:26:30 PM
Posts: 6,
Visits: 11
|
|
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 ?
Rupesh Baheti
Datawarehouse Analyst
OCBC Bank
Singapore
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/19/2009 9:26:30 PM
Posts: 6,
Visits: 11
|
|
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 ;
insert into test12 values ('2009-11-02')
Rupesh Baheti
Datawarehouse Analyst
OCBC Bank
Singapore
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
Regardless of any DATEFORM setting:
create volatile table test12
( load_date date )
on commit preserve rows ;
insert into test12 values ('20091102' (date ,format 'yyyymmdd'))
Dieter
|
|
|
|