Date is going as null to an informix table

kool_cons · Post by **kool_cons** » Wed Oct 19, 2005 2:37 pm

i am loading a table with flat file input which has date field. metadata for source file date field is same as table date field. but when i tried to load into table, date is getting null. it was warning like
"Value treated as NULL
Attempt to convert String value "2005-10-19" to Date type unsuccessful".
I tried with so many date conversions.nothing helped me. SO could you please show some light on me.

kcbland · Post by **kcbland** » Wed Oct 19, 2005 2:56 pm

If you are using the Informix CLI stage, you must use the internal DS date format. Please search the forum as various solutions have been covered. This isn't an "issue", it's a feature.

kool_cons · Post by **kool_cons** » Wed Oct 19, 2005 3:12 pm

Could you provide the link of the forum if possible. i could not find..
thanks for your help

roy · Post by **roy** » Wed Oct 19, 2005 3:40 pm

Hi,
Using date data type as your source column in native stage like CLI (not ODBC) will result inthe internal DS date format.
To load that date to Informix means using the same date data type.
I've done it several times with no problem in the past. (even timestamps simply get zero time values)
Perhaps there is a configuration difference between your source and target DB's regarding dates:?: (Do you have any such environment variables set in your dsenv file?)

IHTH,

kool_cons · Post by **kool_cons** » Wed Oct 19, 2005 4:09 pm

problem is solved if i input the field like mm-dd-yyyy instead of yyyy-mm-dd.
thansk guys.

prabu · Post by **prabu** » Wed Oct 19, 2005 8:40 pm

kool_cons wrote:problem is solved if i input the field like mm-dd-yyyy instead of yyyy-mm-dd.
thansk guys.

maybe a redundant answer but....

alwasy rt y to say view data from any input stage and see what the sample data looks like and convert it accordinly

kcbland · Post by **kcbland** » Wed Oct 19, 2005 8:53 pm

Not really, the Informix CLI stage is unlike ODBC and Oracle OCI stages in that DATE data type fields MUST be presented to the CLI stage in the DataStage BASIC internal date format. Viewing the data has rather odd results in newer released of DS, in that you see the internal number followed by an ISO date. Ex: 12345 (2004-07-14) (or whatever that really works out to be. 12345 is the integer number of days since day zero in the DS BASIC language.

So, if you change the metadata to CHAR, VARCHAR, or something else character based, the CLI stage doesn't know that the data in the date column is a date, so it doesn't require it to be in internal format. Because of that, it passes it straight thru. Now, you must have the date data in the right string format for casting into the Informix database. If you're lucky, it works.

Many companies were faced with either changing all DATE metadata columns in their jobs to CHAR in order to get ISO dates to load, or putting ICONV functions on all derivations prior to streaming into the CLI stage. Neither method is palatable if your data has a lot of dates. Folks used to complain that the Informix plugin should work just like the ODBC and Oracle, but nothing was ever done about it. As I stated earlier, it's a feature, not a bug.