DSXchange

I have a date(YYYYMMDD) in a number format.

For example:
- 20050810 become 50810
- 20150810 become 150810

I have to insert this number in a oracle table where the field is a date.

I'm using Iconv(DATE, "DYMD[2,2,2]") but doesn't working.

Does anybody help me?

Regards,

Fernando

Hi Fernando,

The solution has already been mentioned several times in this forum. Please use the search facility.

Any Date Conversion has two go through two steps

1) First it has to be converted into the required format using Iconv, which will give the output in the internal format. This internal format is not suitable to be directly inserted into the target database.

2) Therefore you need to use Oconv on the result of the Iconv, to convert it into the correct required format and this value can be directly inserted into the database table.

You can combine both the above steps like shown below

If it's in YYYYMMDD format, then you need either [4,2,2] (not [2,2,2] which specifies a two-digit year) or nothing after the DYMD part of your Iconv() specification.
Both of the following will work for YYYYMMDD format:

• Iconv(TheDate, "DYMD[4,2,2]")
  
  Iconv(TheDate, "DYMD")

You then need to use Oconv to get that result into whatever format your Oracle date picture specifies.

However, since there are not data types in DataStage server edition, you may as well just treat the incoming date as a string and format it appropriately (provided you're certain that all incoming values are valid). For example:

• Fmt(TheDate, "L####-##-##")
  
  TheDate[1,4] : "-" : TheDate[5,2]: "-" : TheDate[7,2]