Timestamp Conversion

reachmexyz · Post by **reachmexyz** » Mon Apr 06, 2009 9:19 am

Hello All

I am reading a field name POLICY_EFF_DT from oracle table using OCI Stage and output is fed to dataset.
POLICY_EFF_DT datatype is Date .
When i try to read the field direclty i got an error like
"No datatype conversion form timestamp to date"
Then i modified the quer like
To_date(policy_eff_dt, 'YYYY:MM:DD HH24:MI:SS').
When i run the job, i got the below error.
The modify operator has a binding for the non-existent output field "POLICY_EFF_DT".

Same query executes successfully in Oracle.
Do i need to any transformations for date field in Parallel edition.

In oracle it looks only Date datatype exists eventhough it has timestamp value.
How can i read this field in OCI stage in parallel.
In OCI Date only takes "YYYY:MM:DD" format.
Do i need to convert date to char and read the field

chulett · Post by **chulett** » Mon Apr 06, 2009 9:43 am

You use TO_CHAR() when selecting, not TO_DATE() as it already is one in Oracle as thus only used when loading data into a DATE field. Then the appropriate StringTo?? function can convert it to whatever you need, date or timestamp, inside the job depending if you need the time portion or not.

And lose the colons in the date mask, dashes would be more appropriate there.

girija · Post by **girija** » Mon Apr 06, 2009 11:25 am

Use "To_date(policy_eff_dt, 'YYYY:MM:DD HH24:MI:SS') POLICY_EFF_DT " in your query.

tsamui · Post by **tsamui** » Tue Apr 07, 2009 3:14 am

Change the POLICY_EFF_DT data type to Timestamp. I think OCI stage will able to read the date properly.

Actually if you import the metadata of the table by 'Import Table Definition' functionality, the data type of the column will be Timestamp.

lohit43 · Post by **lohit43** » Mon Jun 29, 2009 1:59 pm

Use "To_date(policy_eff_dt, 'YYYY:MM:DD HH24:MI:SS') as POLICY_EFF_DT " in query.

dsuser_cai · Post by **dsuser_cai** » Mon Jun 29, 2009 2:10 pm

try using, to_char(<column_name>, 'yyyy-mm-dd hh24:mi:ss') and make the data type as varchar 19 or 26.

Kryt0n · Post by **Kryt0n** » Mon Jun 29, 2009 3:25 pm

the vital response...

tsamui wrote:Change the POLICY_EFF_DT data type to Timestamp. I think OCI stage will able to read the date properly.

Actually if you import the metadata of the table by 'Import Table Definition' functionality, the data type of the column will be Timestamp.

An Oracle date still holds the time field and is therefore actually a timestamp.

If you want just the date, do a
to_char(<field>, 'YYYY-MM-DD') policy_blah_blah

i.e. leave out the time part as DataStage will expect a timestamp datatype if time is specified.

Gladiator1 · Post by **Gladiator1** » Mon Jul 13, 2009 3:50 am

I have the same problem,in Oracle the EFF_DT field has a DATE datatype.When i try to use in an Oracle Enterprise stage and supplying the below query

SELECT to_char(PIP.EFF_DT,'YYYY-MM-DD') as START_DT
FROM table

it is giving the error

Error when checking operator: When binding output interface field "START_DT" to field "START_DT": No default type conversion from type "ustring[max=75]" to type "date".

Help me!!

priyadarshikunal · Post by **priyadarshikunal** » Mon Jul 13, 2009 5:08 am

SELECT to_char(PIP.EFF_DT,'YYYY-MM-DD') as START_DT
FROM table

First try to go through the error message where it says the columns' datatype is Date and by using to_char() you are passing string to it.

Use to_date() or change the field type as varchar.

Gladiator1 · Post by **Gladiator1** » Mon Jul 13, 2009 8:31 am

Thanks Priya..

I found a solution by getting the EFF_DT accepted as timestamp field and later on in the job i typecasted using TimestamptoDate funtion in the transfromer before inserting it into the target table.