Oracle 9i timestamp(6) feature and error ORA-01830
Moderators: chulett, rschirm, roy
Oracle 9i timestamp(6) feature and error ORA-01830
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
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
Code: Select all
TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS:FF')
IHTH
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
-
- Participant
- Posts: 36
- Joined: Mon Mar 27, 2006 12:23 am
- Location: Chennai
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: