Page 1 of 1
oracle date problem
Posted: Wed Dec 15, 2004 9:07 pm
by elvishada
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?
Posted: Thu Dec 16, 2004 12:08 am
by ray.wurlod
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.
Posted: Thu Dec 16, 2004 12:48 am
by chulett
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.
Posted: Thu Dec 16, 2004 1:17 am
by ray.wurlod
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.
Posted: Thu Dec 16, 2004 4:04 am
by elvishada
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?
Posted: Thu Dec 16, 2004 7:52 am
by chulett
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.
Posted: Wed Dec 22, 2004 2:39 am
by elvishada
in the oracle database ， the field is date
in the datastage define， the field sql type is date
Posted: Wed Dec 22, 2004 10:20 am
by chulett
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:
For a Timestamp field in DataStage, it is expecting the format to be: