Loading Date

Database
Enthusiast

Loading Date

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.
6 REPLIES
Enthusiast

Re: Loading Date

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

Enthusiast

Re: Loading Date

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

Re: Loading Date

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

Enthusiast

Re: Loading Date

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

Re: Loading Date

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')
Junior Contributor

Re: Loading Date

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