One Star

[resolved] Invalid Date Input for DB2

Hi,
I am having a simple job to write XML file contents into a Database table (DB2) , current data type for the xml field is Date and the format is "yyyy-MM-ddTHH:mm:ss" , the column's type is Date in the database table. When we write the data into the DB table I have this DB errror : Unparseable date: "2014-02-23T00:00:00"
I tried to formatt the date with Expressions in th tMapEditor like below but I have an error type equivalence;
"Type mismatch: can not convert string to a date"
13 REPLIES
Moderator

Re: [resolved] Invalid Date Input for DB2

Hi,
Could you please upload your Mapping editor screenshot of tMap into forum? Is it working when you write XML file contents(Date) into tlogrow instead of DB2?
In addition, Parse String to Date:
TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",row1.Value)

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seventeen Stars

Re: [resolved] Invalid Date Input for DB2

Hi Seiif,
you read a text input (xml is text) and therefore you have convert this piece of text into a java Date object. As Sabrina already explained (but a bit incomplete) you need the function to parse text:
TalendDate.parseDate("yyyy-MM-ddTHH:mm:ss",row1.Value)

configure the output flow of your tMap according to the database table or simply check if your DATE field is configured with the Java type Date.
If you are sure the xml file contains always the data content in this format you can left out this conversion in the tMap and configure the schema field for the xml file already as Date with the pattern "yyyy-MM-ddTHH:mm:ss".
The xml reading functionality will in this case try to convert the text content into a Data object.
One Star

Re: [resolved] Invalid Date Input for DB2

Hi Sabrina,
I have the same error when I wrire XML file contents(Date) into tlogrow instead of DB2.
I just load the Mapping editor screenshot in the this post
Four Stars

Re: [resolved] Invalid Date Input for DB2

Hi,
There is no need of variable, remove that and directly use above parsing expression for that column in tMap output.
Vaibhav
One Star

Re: [resolved] Invalid Date Input for DB2

Hi Jolling,
Even when I configure the schema field for xml file as Date with the pattern "yyyy-MM-ddTHH:mm:ss", I still have the same error:
Unparseable date: "2014-02-23T00:00:00"
Unparseable date: "2014-02-23T00:00:00"
Unparseable date: "2014-02-23T00:00:00"
...
One Star

Re: [resolved] Invalid Date Input for DB2

Hi Vaibhav,
I used this parsing expression for the date column in tMap output ""dd-MM-yyyy"" direclty without the variable , and I still have this error Unparseable date:
Unparseable date: "2014-02-23T00:00:00"
Unparseable date: "2014-02-23T00:00:00"
...
Four Stars

Re: [resolved] Invalid Date Input for DB2

Now the problem is different..
Your input date ("2014-02-23T00:00:00") format also has time stamp and you are trying to convert it to date with dd-MM-yyyy?
Use tJavaRow component before tMap, extract Year, Month and date independently from date string, restructure your date as per dd-MM-yyyy and then parse it to date to get the results.
Refer to talend routine related to date at
https://help.talend.com/search/all?query=TalendDate+Routines&content-lang=en
I think you got an idea... try couple of sample date formatting using simple tjava component and you would be able to do the trick to solve the problem.
Vaibhav
One Star

Re: [resolved] Invalid Date Input for DB2

I am not trying to convert the date , I am just trying to find a solution for this error. At first, I thought that was beacause of the Date field's type in the database, but even when I change the database connection with tLogrow component I still had the same error. Sorry if I could'nt explain my request clearly.
Four Stars

Re: [resolved] Invalid Date Input for DB2

This is a problem when formatting input datetime to other format. It has nothing to do with the tlogrow connection.
If you read your input date as string data type and then connect output as tlogrow, then it will not throw error. You can check this.
But if you want to read input date as date time and convert it to your format you will have to do some transformations....
Vaibhav
One Star

Re: [resolved] Invalid Date Input for DB2

I tried to fix the error with tJavaRow componenet writing this code :
Date dteSoldeDT = TalendDate.parseDate("yyyy-MM-ddTHH:mm:ss", input_row.DteSolde); 
Integer dd = TalendDate.getPartOfDate("dd",dteSoldeDT);
Integer MM = TalendDate.getPartOfDate("MM",dteSoldeDT);
Integer yyyy = TalendDate.getPartOfDate("yyyy",dteSoldeDT);
output_row.DteSolde = TalendDate.parseDate("dd-MM-yyyy", dd.toString()+"-"+MM.toString()+"-"+yyyy.toString());

Knowing that the type of the field is String in the Input row and Date (format dd-MM-yyyy)in the tMap component.
But I still have the same problem..
Unparseable date: "2014-02-23T00:00:00"
Below a screenshot which describes the modificaton
One Star

Re: [resolved] Invalid Date Input for DB2

Just another comment; The Date fields in the xml file are all written in this format :
<DteSolde>2014-02-24T13:37:11</DteSolde>
Four Stars

Re: [resolved] Invalid Date Input for DB2

Hi,
First line of parsing is not required... Remove that.. it should be Ok.
Integer dd = TalendDate.getPartOfDate("dd",input_row.DteSolde);
Integer MM = TalendDate.getPartOfDate("MM",input_row.DteSolde);
Integer yyyy = TalendDate.getPartOfDate("yyyy",input_row.DteSolde);
output_row.DteSolde = TalendDate.parseDate("dd-MM-yyyy", dd.toString() "-" MM.toString() "-" yyyy.toString());
output_row.DteSolde - is your final transformed date...
Also refer to discussion at http://www.talendforge.org/forum/viewtopic.php?id=9769
This also hints about the new way of transforming date using DateFormat
Check if it is ok.
Vaibhav
One Star

Re: [resolved] Invalid Date Input for DB2

thanks for your help