I have a sequential file that I am doing a lookup to a DB2 Table that has a date in on of the key fields (used for lookup) I have tried changing the column format on the date in the sequential file to date and char but I get the following error doing the lookup.
Attempt to convert String value "2002-05-31" to Date type unsuccessful
Any Suggestions?
Brian G Nagel
Date Conversion Problem DB2
Moderators: chulett, rschirm, roy
Hi Brian,
First of all, you must know that DataStage stores date types into its internal date format (which is numeric.. more specifically, the number of days since, or before 1967-12-31) rather then into a string format. It is attempting to read the string type field from your seq file as a date type field in order to match with the date column from your lookup table but runs into a type mismatch.
Now, what I can think of doing is...
- writing your lookup table into an intermediate file in which you could convert the date field from your lookup table to a string format. You could then do the lookup from that intermediate file.
- converting, in a previous step (or job), the string field from your seq file into a date field
- or (if you are using DataStage Server or 5.x and lower), putting the Iconv function in the Key expression field of the look up table (where you do the join) in the your Transform might even work!
Conversions to and from the internal date format can be done with the Iconv (as Mat suggested in your case) or Oconv functions.
Hope it helps
Creo
First of all, you must know that DataStage stores date types into its internal date format (which is numeric.. more specifically, the number of days since, or before 1967-12-31) rather then into a string format. It is attempting to read the string type field from your seq file as a date type field in order to match with the date column from your lookup table but runs into a type mismatch.
Now, what I can think of doing is...
- writing your lookup table into an intermediate file in which you could convert the date field from your lookup table to a string format. You could then do the lookup from that intermediate file.
- converting, in a previous step (or job), the string field from your seq file into a date field
- or (if you are using DataStage Server or 5.x and lower), putting the Iconv function in the Key expression field of the look up table (where you do the join) in the your Transform might even work!
Conversions to and from the internal date format can be done with the Iconv (as Mat suggested in your case) or Oconv functions.
Hope it helps
Creo