well I would suggest is, read the column as varchar2(10) through-out all the stages from source till last oracle stage and then write a USER-Defined Query, in which for that column you use date function.
Insert into <Table name> (Col1,Col2) values (:1,To_date(:2,'MM/DD/YYYY').
There will be no overhead of using stored procedure.
Attempt to convert String value to Date type unsuccessful
Moderators: chulett, rschirm, roy
-
talk2shaanc
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
-
Sainath.Srinivasan
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
John,
The error you receive is from DataStage and not from your RDBMS. You are using the datatype 'Date' in DataStage, which is an integer value. In one of your transformers, you are moving a string field of 'DD-MON-YYYY' format into a DataStage Date field. As DataStage expects its date in integer format (starting from 31/12/1967), it raises the error.
If you want DataStage Date, all you need to do is the IConv inside the OConv. Something like IConv(YourDate, 'D/MDY[2,2,4]').
If you want the RDBMS Date, which can be either of DD-MON-YYYY or DD-MM-YYYY or YYYY-MM-DD format, you need to use the OConv over IConv.
Try writing the result into a sequential file stage. It can give you a better idea.
The error you receive is from DataStage and not from your RDBMS. You are using the datatype 'Date' in DataStage, which is an integer value. In one of your transformers, you are moving a string field of 'DD-MON-YYYY' format into a DataStage Date field. As DataStage expects its date in integer format (starting from 31/12/1967), it raises the error.
If you want DataStage Date, all you need to do is the IConv inside the OConv. Something like IConv(YourDate, 'D/MDY[2,2,4]').
If you want the RDBMS Date, which can be either of DD-MON-YYYY or DD-MM-YYYY or YYYY-MM-DD format, you need to use the OConv over IConv.
Try writing the result into a sequential file stage. It can give you a better idea.