Talend community forum / unparseable date in mysql to salesforce

areales · 2011-03-01 23:53:54

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:

Code:

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)

Thanks for the help in advance.

shong · 2011-03-02 09:57:48

Hi
Do you change the date pattern on tMap? please upload a screenshot of tMap.

Best regards
Shong

areales · 2011-03-03 20:13:01

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.

areales · 2011-03-03 20:16:49

Oops. Image was too big. Here it is...

Uploaded Images

frankEichholz · 2011-03-03 21:10:51

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.

areales · 2011-03-03 22:01:58

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.

Last edited by areales (2011-03-03 22:03:58)

shong · 2011-03-04 03:50:08

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

PrasadJ · 2011-09-06 08:05:53

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.
}

Thanks, Prasad.

Announcement

Unanswered posts

#1 2011-03-01 23:53:54

unparseable date in mysql to salesforce

Code:

#2 2011-03-02 09:57:48

Re: unparseable date in mysql to salesforce

#3 2011-03-03 20:13:01

Re: unparseable date in mysql to salesforce

#4 2011-03-03 20:16:49

Re: unparseable date in mysql to salesforce

#5 2011-03-03 21:10:51

Re: unparseable date in mysql to salesforce

#6 2011-03-03 22:01:58

Re: unparseable date in mysql to salesforce

#7 2011-03-04 03:50:08

Re: unparseable date in mysql to salesforce

#8 2011-09-06 08:05:53

Re: unparseable date in mysql to salesforce

Board footer