Four Stars

Unparseable date: "2017-11-13T09:54:19.182Z"

Hi all, 

I am doing the integration in talend, 

i got the response in SOAPUI for creating new customer account, in that  im getting as

<ns2:LastUpdateDate>2017-11-13T09:25:25.143Z</ns2:LastUpdateDate> format, but i have  20-08-17   date format in database which i have to insert..

 

i.e   i have to convert 2017-11-13T09:25:25.143Z to 20-08-17 format,

 

and also in TMap component, i changed the datatype from string to date format, didn't work.

 

 

Can anyone help me with this?

Rak**bleep**a

81 REPLIES
Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

Unless your date value in your database is held as a String (which I highly doubt), you do not need to worry about the format of the date you insert. Dates are not stored with a format when the type is Date. What you need to do here is convert your String type to a Date before inserting it. To do that, you can use the following code.....

 

routines.TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "2017-11-13T09:25:25.143Z")

The code above will convert the value you used to demonstrate. You will need to replace the actual date String above with your column name.

Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

Hi thanks for the reply,

can you please elaborate on this 

 

"You will need to replace the actual date String above with your column name."

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

talend-TMap -UPDATE_DATEtalend-TMap -UPDATE_DATEDATABASE TABLEDATABASE TABLE

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

OK, it looks like your database is storing your dates as Strings. As such, you will need the following code in your tMap (the column you have highlighted in your screenshot)....

 

row4.LastUpdateDate!=null ? routines.TalendDate.formatDate("dd-MM-yy", routines.TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", row4.LastUpdateDate)) : null
Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

i tried your solution, but not working, 

 

i am giving as 

routines.TalendDate.parseDate("DD/MM/YY" ,"20/08/17")  but getting the same error

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

should i write this code in the same place as you mentioned 

routines.TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "2017-11-13T09:25:25.143Z")

?? 

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

database_datatypedatabase_datatype

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

OK, so the database is holding the value as a Date. You must specify details like this when asking questions on here. Format here is irrelevant. A Date is a number. However, you are receiving the Date as a String so you need to format the date appropriately. It should also be noted that your schema loading data to your database is wrong. The tMap output table shows our Create_Date column as a String. This (if it is feeding the DB directly) must be a Date.

 

Once your schema column types are correct (this is the most important thing), the code you will require will be this (to be put in your tMap output table....in the column I mentioned before)...

routines.TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", row4.LastUpdateDate)
Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

OK will try for this

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

hi i tried this format, it is displaying as 

type mismatch:cannot convert from date to string

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

and in the tmap output table, if i convert from string to date,  it gives an error

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

Can you take a screenshot of your job and post it please? Also, can you give the error you are talking about? 

Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

talend-jobtalend-jobwhen i give date in the tmap-under row4--columnwhen i give date in the tmap-under row4--columnerrorerror

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

Can you show me the schema of your tOracleOutput component please? How did you create this component's schema? Did you import it?

Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

u mean the tables?

yes i connected to the database where i have to insert the data, and the required columns i have added in TMap component

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

Screenshot (622).png

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

I think you have configured your database schema incorrectly. I need to see how your database schema is configured.

Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

means, i didn't get you, 

which details should i provide now

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

database schema detailsdatabase schema details

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

Please follow this tutorial (https://help.talend.com/reader/hULYZJz6QBG0R8nvFrppxQ/EntMthts4SKiXVAFTv9Ypw) on retrieving your schemas from your database. You have a lot that appears to be wrong here. Your database schema appears to be very wrong. 

Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

Screenshot (625).pngScreenshot (626).png

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

OK thanks, i have done the metadata and imported the columns from there and it automatically got added to TMap component

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

can you please tell me where is the mistake

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

I believe your mistake was with your database component schema configuration. The column this question was regarding was shown to be of type Date in your database. Unfortunately your database component's schema seemed to have this column set up as a String. It would never have worked. Now that your schema has been corrected, all you need to do is ensure you convert your date (which you say arrives as a String) to a Date using one of the lines of code I provided.

Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

okay thank you

Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

so now in the toracleoutput component, under the schema, in the DB type it has been set to VARCHAR2 by default, so should i change that datatype to DATE?

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

If you imported your database table schema correctly and your date columns are Date types, your schema should not be set to Varchar. Can you take a screenshot of your db component's config showing the type of schema selected and your database connection settings. Also can you click on the schema button to show me the schema and make sure that the column this question is about is shown?

 

 

Rilhia Solutions
Four Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

database connection detailsdatabase connection detailsedit schema-detailsedit schema-details

Twelve Stars

Re: Unparseable date: "2017-11-13T09:54:19.182Z"

You are using a built-in schema for your DB component. This must be a "Repository" schema and the selected repository schema must be the schema you created following the Talend schema tutorial I sent you. The schema is important. Your Date columns will not be Varchar or String types. Remove your schema from your output table in your tMap and disconnect it from the database component. Correct the schema in your database component (to the repository schema). Then reconnect your tMap to the database component. You will then see your tMap schema is correct. 

Rilhia Solutions