"Conversion failed when converting date and/or time from character string"

[Former Member]

Hi All,

As I am facing a different kind of error, where in receiver JDBC Adapter throughing with an error "Conversion failed when converting date and/or time from character string".

Actually, some invoices, generate with created date, some generate with "00000000"..Inordertomakeso, we have done in the mapping as attached.

My concern is, when evercreated date is like (eg: 2015-02-22) so on, it will get reflected exactly, but when they type "00000000", it throughing with an error "Conversion failed when converting date and/or time from character string". Actually, it should get reflected as "NULL"..Its not happening in this case.

Please find any alternative solution need to be taken in PI or SQL

[Former Member · Accepted solution]

Hi,

Thanks for replying Azhar and ambuj..

1. I have maintained the XSD type of target field GIDate as xsd:string

2. The receiver team was not accepted to send dummy date. Their requirement is to pass the NULL if the field date value coming it as NULL (for "00000000")

3. How to supress this field date if the input is coming as 0's.

In SQL, they have defined the field GIDate(as Datetime). If we change the datetime to varchar then for the success invoices their would we an problem.

So, Please suggest any other alternative solution that when the date is of 0's, it should replicate it as "NULL".

OR

If it is created date= 0's, from PI level it should pass the system time to the GIDate.

Any solution for this kind of requirement..?

[nitindeshpande · Accepted solution]

Hello Ram,

Use the datatype for this field as string. And then have "If" condition.

If the GIDate field equal to "000000.. then pass Current Date, and else if there is some other value coming in the field then pass the GIDate itself.

I hope this should resolve your problem.

Regards,

Nitin Deshpande

[azharshaikh · Accepted solution]

Hi Ram,

You can map in following way to achieve this. Use Standard Date Function - currentDate, double click on this function & enter the required date format in properties (by default the date format is yyyy/MM/dd). It will map the Current System date to the target field incase input value is 0's.

Regards,

Azhar

[Former Member]

Thank you Azhar....It's working fine

[former_member183816]

I think, in your database, that field is defined as datetime, so you need to pass a valid date from your end (instead of null). Or change the type of field to varchar in db or send some dummy date agreed upon with db team.

[azharshaikh]

Hello Ram,

What is the XSD type you have defined in PI for target field GIDate ? If its xsd:date then this might fail when you pass invalid date of 0's. Check following options.

1. Try to change the xsd type to string in PI and check if it helps

2. Or you should pass some dummy date (as agreed by Receiver team) to target incase input is sending 0's

3. Or you can supress this field if date in input is coming as 0's

Hope it helps

Regards,

Azhar