BULK loading DATE data type error

champsdb · Post by **champsdb** » Mon Oct 02, 2006 2:26 pm

I am trying to load DATES using oracle enterprise using a PX job. I have dates comming in a 'yyyyddmm' format. I converted that to DATE in transformer using StringtoDate(inputlink, '%yyyy%mm%dd'). Its not able to but load the data using oracle enterprise stage.

Thnx,

ratikmishra1 · Post by **ratikmishra1** » Mon Oct 02, 2006 2:39 pm

Can you check if it works if the target is a dataset or a file set instead.
If yes, the problem may be in the data and that needs to be taken care of in the transformer.

champsdb · Post by **champsdb** » Mon Oct 02, 2006 2:55 pm

Is there a way to check that the problem is with Data? Right now i am storing it a char8 and it LOOKS ok to the eye...

champsdb · Post by **champsdb** » Mon Oct 02, 2006 3:13 pm

This is the error msg:

Record 1: Rejected - Error on table STG_PROVIDER_COST, column R263_FISCAL_DATA_E
ND.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

ray.wurlod · Post by **ray.wurlod** » Mon Oct 02, 2006 3:18 pm

This is an Oracle error. To find out more execute the following command on the DataStage server.

Code: Select all

oerr ORA 1841

Basically, however, your data (column R263_FISCAL_DATA_END) contains at least one date with the year less than -4713, zero, or larger than +9999. This is not acceptable to Oracle when storing as a DATE data type.

My guess is that you have a zero date as a default; you need to intercept this and provide an alternative default.