[resolved] unparseable date in mysql to salesforce
I am going from mysql to salesforce and I am trying to insert records that have a date value in one of the fields. In mysql, the format is "yyyy-mm-dd hh:mm:ss" and I know the format that salesforce accepts in a datetime data type is "yyyy-mm-ddThh:mm:ss.000Z".
I am just doing a simple mapping in the tmap without any expressions. I get the following error when I run my job:
Exception in component tSalesforceBulkExec_1 java.lang.RuntimeException: Unparseable date: "2010-10-06T16:53:27.000Z" at routines.system.ParserUtils.parseTo_Date(ParserUtils.java:164)
Hi Sanket
This error is not caused by parallel execution, but the different date formats in the column, you need to check the length of the string which need to be parsed to a date, and use different date pattern to parse it.
BR
Shong
I have attached my tmap. I will also add that I am able to write successfully to other date data type fields on other objects in my project. Here I am writing to a custom field of type date/time(salesforce) on the Contact object. The other successful writes were on custom objects and custom fields. Don't know why I am getting this error only here. Hopefully this will help debug the issue. Thanks.
Because you have two different date-formats (left side: yyyy-mm-dd hh:mm:ss / right side: yyyy-MM-dd'T'HH:mm:ss'.000Z'), you have to format the date of the left side to the format of the right side "yyyy-MM-dd'T'HH:mm:ss'.000Z'".
Try to replace on the right side "salesforce_flattened_user.member_since" with "TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss'.000Z'", salesforce_flattened_user.member_since)".
For different date related routines you can use the expression builder. In the expression builder are the routines and their usage described.
Thanks for the suggestion.
I tried that and I got the following error in the tMap > Type mismatch: cannot convert from String to Date
Both fields are date data types so I'm not sure why it's thinking its a String. Is the output of the function a String?
The the thing I don't get also is that when I run the job, I get the original unParasable date error but it still writes to Salesforce. Not sure why that happens either. And like I said in my original post, I am able to write to a date data type field with other objects just fine. It's just this one on the Contact object on Salesforce I am having trouble with.
I will also mention that the unParasable error occurs on the tSalesforceBulkExec component.
Hello
The return result of the function is a string, however the data type is Date on schema, that's why you get cannot convert from String to Date. Don't use this method to convert a date to string. What's the date pattern defined in your contact object? As you said, the data are inserted into SalesForce even there is unParasable error occurs on the tSalesforceBulkExec, try to change the date pattern in right side to yyyy-mm-dd hh:mm:ss
Best regards
Shong
Hi In Java I am fetching datetime value from mysql and want to insert it in salesforce database.But due to datetime format is different it's not getting inserted properly in salesforce.I can not use simpledateformat also for conversion. I need a help for this.I am sharing code part also. ResultSet? tradesResultSet = statement.executeQuery("SELECT FROM mt4_trades limit 10"); // Fetch records from MYSQL while (tradesResultSet.next()) { java.sql.Timestamp mysqlTimestamp = tradesResultSet.getTimestamp("Open_Time?"); // Get MYSQL time java.util.Date dbSqlDateConverted = new java.util.Date(mysqlTimestamp.getTime()); // conver to JAVA date hm.put("Open_Time?", dbSqlDateConverted); // Put in map java.util.Date d = (java.util.Date) hm.get("Open_Time?"); // get date SObj.setField("Open_Time?c",d); // set in Salesforce field. }
Hi, I am using date format as "yyyy-MM-dd'T'HH:mm:ss" and i am still getting parsing error on selecting parallel execution. Could anyone let me know the resolution. Thanks, Sanket
