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?
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.....
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.
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."
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
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
should i write this code in the same place as you mentioned
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)...
Can you show me the schema of your tOracleOutput component please? How did you create this component's schema? Did you import it?
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
I think you have configured your database schema incorrectly. I need to see how your database schema is configured.
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.
OK thanks, i have done the metadata and imported the columns from there and it automatically got added to TMap component
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.
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?
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?
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.
Introduction to Talend Open Studio for Data Integration.
Practical steps to developing your data integration strategy.
Create systems and workflow to manage clean data ingestion and data transformation.