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.
Date is going as null to an informix table
Moderators: chulett, rschirm, roy
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle