oracle date problem

elvishada · Post by **elvishada** » Wed Dec 15, 2004 9:07 pm

i write the sql in the oci stage

SELECT to_date('#etldate#','yyyy/mm/dd') AS etldate
FROM T_WORKDATE T_WORKDATE

Etldate is defined in the parameters of the Job Properties

when i view data , the error is DSBrowse..ORAOCI9_0:OCI DATE conversion failed.

how can i change a string to the date type in the oci stage?

ray.wurlod · Post by **ray.wurlod** » Thu Dec 16, 2004 12:08 am

Is the job parameter in YYYY/MM/DD format?

Is the parameter type Date? In this case, beware that DataStage may convert the date to internal format. Check by outputting the value into a text file. If it does, you'll need an Oconv() function in your derivation.

The Oracle TO_DATE function is perfectly OK, provided what you feed it exactly matches the date picture in the second argument.

chulett · Post by **chulett** » Thu Dec 16, 2004 12:48 am

If the parameter type is 'Date', DataStage will put it into YYYY-MM-DD format - which would cause problems for your TO_DATE function as coded.

ray.wurlod · Post by **ray.wurlod** » Thu Dec 16, 2004 1:17 am

To expand on what Craig said, "YYYY-MM-DD" is not the same as the "YYYY/MM/DD" you specified in your date picture. Change the date picture argument of TO_DATE such that it uses "-" as the delimiter character.

elvishada · Post by **elvishada** » Thu Dec 16, 2004 4:04 am

i change the parameters type to the date,the problem is solved,
but when i insert the oracle table , it

ntitled1..Transformer_2: The value of the row is: etldate = 31-十二月（or DEc）-2004
Untitled1..Transformer_2: ORA-01843: not a valid month
Untitled1..Transformer_2: DBMS.CODE=ORA-01843

how can i do, change datastage or oracle?

chulett · Post by **chulett** » Thu Dec 16, 2004 7:52 am

How is the field defined in the Oracle table? How is it defined in the DataStage job? What stage are you using to access Oracle? If applicable, are you using Custom or Generated SQL to insert into Oracle? These can all affect the answer to your question.

elvishada · Post by **elvishada** » Wed Dec 22, 2004 2:39 am

in the oracle database ， the field is date
in the datastage define， the field sql type is date

chulett · Post by **chulett** » Wed Dec 22, 2004 10:20 am

Assuming that you are using one of the OCI stages to write to your target, you need to know that the OCI stages need Date or Timestamp data in a specific format. You can read the Oracle OCI Technical Bulletin that ships with the product for the gory details, but dates should be in this format:

Code: Select all

YYYY-MM-DD

For a Timestamp field in DataStage, it is expecting the format to be:

Code: Select all

YYYY-MM-DD HH24:MI:SS