DSXchange

Hi,
I am using DataStage 7.5 Server Edition with Oracle 9i.
I have source table A, column PostingDate defined as timestamp(6) e.g
2000-08-31-20.49.03.932430

I ahve target table B, column PostingDate1 defined as timestamp(6)

When I look in the target stage, I have defined it as column generated SQL and the Sql statement looks like
INSERT INTO TEMP_table (POSTINGATE1) VALUES (TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS'))

When I run the job, it gives OracLe error
ORA-01830: date format picture ends before converting entire input string

Thks
Ketfos

You need to trim the milliseconds(I think its not even milliseconds, it much less than that, but I shall say it as milliseconds) part if you have to load it into Oracle. That is the reason it giving you that error. If you want milliseconds as part of your output then use user-defined sql and then declare your timestamp as Else, If you don't need it, you can use FIELD function to trim of the trailing seconds part from your input.

IHTH

You may need to use TO_TIMESTAMP instead of TO_DATE for that field type. Or not.

Try to search for the error "ORA-01830" in google. It will return you a lot of posts, why the error is caused and how to fix it. Its the time part thats actually creating the problem. You are converting one way and the data base expects in a different way. Maybe HH instead of HH24.

Nope, it's the milliseconds which the current mask does not include. Hence the message about running out of 'format picture' before running out of timestamp.

Aye Aye Captain

Target data type must be timestamp ....
Use TO_TIMESTAMP(:1,'YYYY-MM-DD HH24:MI:SS:FF')


Thanks,
Anupam

how do u want it in the target ?
the input date with timestamp is in one formate and the target is in another form.
to_date(':1,YYYY-MM-DD HH24:MI:SS')
you have this millisecond in ur source .
so its better to use To_TIMESTAMP()

Hi,
Thanks everybody for help.
I was able to insert the timestamp datatype in the target table using user defined sql suggested in the earlier posts correctly.

INSERT INTO TEMP_table(POSTING_DTS) VALUES (TO_timestamp(:1, 'YYYY-MM-DD HH24:MI:SS:FF'))

I can view the data in the target table correctly using sql command or sqlplus command or TOAD.

But when I view in the datastage job i.e in the target stage, it only shows

2000-08-31 20:49:03 although using TOAD it gives me complete value as

2000-08-31-20.49.03.932430

Why it is truncated when I view in datastage.
The target column type is timestamp length is 26.

Ketfos

What is the display tab set to..I guess default is 20.Change it to 26 and you will be fine.

IHTH

Hi,
The display is set to 26.
When I view in datastage it is trucnated as
2000-08-31 20:49:03

WhenI query the table, it shows full 26 char as
2000-08-31-20.49.03.932430

Thks

Ketfos

Perhaps that's just a "feature" of View Data ?