DateTrans error in mapping

[Former Member]

Hi Experts,

Trying to transform the Source date format "YYYY-MM-DD" to the target format "DD-MM-YYYY" using "DATETRANS" function in XI. Successful in MONI and in RWB,on Integ.Engine throws an error. Below is the content of the Audit log. Need help on this.

Time Stamp Type Description

2009-09-19 17:38:56 Information The message was successfully received by the messaging system. Protocol: XI URL: http://pidev:50000/MessagingSystem/receive/AFW/XI Credential (User): PIISUSER

2009-09-19 17:38:56 Information Using connection JDBC_http://sap.com/xi/XI/System. Trying to put the message into the receive queue.

2009-09-19 17:38:56 Information Message successfully put into the queue.

2009-09-19 17:38:56 Information The message was successfully retrieved from the receive queue.

2009-09-19 17:38:56 Information The message status was set to DLNG.

2009-09-19 17:38:56 Information Delivering to channel: JDBCReceiverHRMasterData

2009-09-19 17:38:56 Information JDBC Adapter Receiver processing started, required QoS ExactlyOnce

2009-09-19 17:38:56 Information JDBC Adapter Receiver Channel JDBCReceiverHRMasterData: processing started; party / service Sys_Sample_Two_D.

2009-09-19 17:38:56 Error Could not execute statement for table/stored proc. "HR_DEP_MST" (structure "InsertStatement") due to java.sql.SQLException: ORA-01843: not a valid month

2009-09-19 17:38:56 Error JDBC Message processing failed, due to Error processing request in sax parser: Error when executing statement for table/stored proc. 'HR_DEP_MST' (structure 'InsertStatement'): java.sql.SQLException: ORA-01843: not a valid month

[Former Member · Accepted solution]

The Stored Proc is expecting date in a diff format... Hence you are gettign theerror

'HR_DEP_MST' (structure 'InsertStatement'): java.sql.SQLException: ORA-01843: not a valid month

Check with your DB team to find the correct format.

Check in RWB(Adapter) if the date format is as-per the Stored proce requirements.??

-Siva Maranani

[Former Member · Accepted solution]

Hi,

I see the date getting converted into the desired format in SXMB_MONI after mapping. Check in RWB(Adapter) if the date format is as-per the Stored proce requirements.?? How do I do this in RWB?

I could achieve this using ToDate function on the target database but I wanna try this using Date func in XI. Pls help.

[former_member187339 · Accepted solution]

Hi Taro,

Is the date format in DB side correct?

Should it be *"DD-MM-YYYY" * and not this *"MM-DD-YYYY" *. Confirm this.

Also Try doing this:

1. Change the Field carrying Date value in target side (DB Side) to xsd:string instead of Date

2. Do a date trans in message mapping (to the desired format)

3. Activate all objects

4. Test your interface

Regards

suraj

Edited by: S.R.Suraj on Sep 21, 2009 3:28 AM

[Former Member · Accepted solution]

Hi, Can u pls send a udf which transforms the date format on the target side..

[Former Member]

Target date format and your mapping date format has to match. check with JDBC resource to confirm the date format.

Regards,

Pavan

[prateek]

Recheck with the receiver side database team. The date format should be MM-DD-YYYY or DD-MMM-YYYY.

Regards,

Prateek

[Former Member]

Hi Taro,

Are you getting the xml payload after message mapping in DD-MM-YYYY format. First check the payload. Then if you are getting then as siva suggested this format is not what the database is expecting. They would have defined this column in a certian format. So there may be a mismatch. So ask them the format and send the same format.

Regards,

---Satish

[former_member750652]

Hi Taro,

Look like the date format that the target database expecting is not in match with that your mapping is transforming.Please check the date format of oracle data base. Please check the following link that deals with similar issue in ORACLE databse.

Thanks,

Ram.