Conversion from type "timestamp" to type "dat

saraswati · Post by **saraswati** » Mon Oct 24, 2011 12:28 pm

In Job1, I am getting a column CREATED_DT in an oracle table populated with current date using datastage date function CurrentDate().This column is having DATE datatype.

In Job2, this column CREATED_DT from the table which got loaded in Job1 is mapped to another column CREATED_DT in a different table having DATE as datatype.

While running the job,I am getting the following error:

Oracle_Enterprise_0: Error when checking operator: When binding output interface field "CREATED_DT" to field "CREATED_DT": No default type conversion from type "timestamp" to type "date".
Please let me know how to fix this error.

SURA · Post by **SURA** » Mon Oct 24, 2011 2:10 pm

you can you to_char in select to have a specific format or you can check the type conversion function in tfm.

DS User

saraswati · Post by **saraswati** » Mon Oct 24, 2011 2:32 pm

Let me know what exactly needs to be done?

saraswati · Post by **saraswati** » Mon Oct 24, 2011 3:03 pm

can anyone help me on this to fix the error?

saraswati · Post by **saraswati** » Mon Oct 24, 2011 3:41 pm

When I changed the source query for the column to:

TO_CHAR(CREATE_DT,'MM/DD/YYYY')

then I am getting this error:

>##E IIS-DSEE-TFIP-00011 17:39:34(001) <Oracle_Enterprise_0> Error when checking operator: When binding output interface field "CREATE_DT" to field "CREATE_DT": No default type conversion from type "string[max=75]" to type "date".

Please help if there is any other solution.

SURA · Post by **SURA** » Mon Oct 24, 2011 4:34 pm

You are passing the char to date. When you pass the date as an input, you need to mention the date format.

1. Use view data and find the format of the date.

2. It is not a bad idea to use to_char , but in that case you might need to use StringToDate function Or you can use to_date.

DS User

saraswati · Post by **saraswati** » Mon Oct 24, 2011 8:39 pm

I tried using TO_DATE over TO_CHAR but still I am facing the same error.

Please suggest something different.

jiegao · Post by **jiegao** » Mon Oct 24, 2011 8:50 pm

Try using TO_CHAR(Create_DT, 'YYYY-MM-DD HH24:MI:SS')

saraswati · Post by **saraswati** » Mon Oct 24, 2011 9:08 pm

I am getting this error after using the above advice:

>##E IIS-DSEE-TFIP-00011 23:07:44(000) <Oracle_Enterprise_0> Error when checking operator: When binding output interface field "DW_CREATE_DT" to field "DW_CREATE_DT": No default type conversion from type "string[max=75]" to type "date".

SURA · Post by **SURA** » Mon Oct 24, 2011 9:50 pm

The error says you are tying to pass the string to date value. Can't you use StringToDate function ?

DS User

rohit.ka07 · Post by **rohit.ka07** » Tue Oct 25, 2011 1:45 am

Saraswathi: you said you populated current date value to a date field and the same date field you're mapping to another date field in second job. since you are mapping date field to date field you should not get this error. check in job from source side to target side whether it is populating as date only till end. also check the lenght of this field. it should be same everywhere.

suse_dk · Post by **suse_dk** » Tue Oct 25, 2011 1:51 am

Read the connectivity guide for Oracle on mapping of oracle datatypes to datastage datatypes.

saraswati · Post by **saraswati** » Tue Oct 25, 2011 9:15 am

I am getting this error even after using StringToDate function:

This below error is from the oracle stage:
Oracle_Enterprise_0: Error when checking operator: When binding output interface field "DW_CREATE_DT" to field "DW_CREATE_DT": No default type conversion from type "string[max=75]" to type "date".

As mentioned earlier,I populated current date value using function CurrentDate() in transformer to a date field and the same date field I am mapping to another date field in second job.Since both are mapping date field to date field there should not be an error.

It is really strange.

Can anyone take a look on it?

ray.wurlod · Post by **ray.wurlod** » Tue Oct 25, 2011 10:12 am

saraswati wrote:Can anyone take a look on it?

Of course. Your official support provider can. That's what you're paying them to do.

saraswati · Post by **saraswati** » Tue Oct 25, 2011 4:52 pm

Did anyone ever faced this issue before?

DSXchange