[resolved] Unparseable date

Eight Stars

[resolved] Unparseable date

Hi,
I have created a job to read data from an XML file and output the data into multiple database tables.
This XML file contains date fields where the date value is given as follows:
1982-05-04T00:00:00.000+01:00

In the schema defined for this XML file, when I give the date format as
"yyyy-MM-dd'T'HH:mm:ss.SSSXXX"
I get the error message "Illegal pattern character 'X'".
For all other date format patterns I get the message: Unparseable date: "1982-05-04T00:00:00.000+01:00"
Can anyone tell me what should be the date format pattern I should provide?

Accepted Solutions
Highlighted
Four Stars

Re: [resolved] Unparseable date

Hi Ketan,
Check this,
http://stackoverflow.com/questions/2375222/java-simpledateformat-for-time-zone-with-a-colon-seperato...
You can get the timezone offset formatted like +01:00 with the SimpleDateFormat in Java 7 (yyyy-MM-dd'T'hh:mm:ss.SSSXXX), or with the Joda's DateTimeFormat (yyyy-MM-dd'T'hh:mm:ss.SSSZZ)
It may help.
Thanks
Vaibhav

View solution in original post


All Replies
Highlighted
One Star

Re: [resolved] Unparseable date

Hi Ketan,
You can try the below approach.
Read the field as string and in tMap parse it to date
TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS", column_name)
I'm not aware of the +01:00 which is mentioned.
As per this, your output will be
1982-05-04 00:00:00.000
~Berin
Highlighted
Four Stars

Re: [resolved] Unparseable date

Hi Ketan,
This last +1 must be related to timezone adjustment...
What is your input format is this date/string... if you can read it as as string, then lots of possibilities are there... please confirm the data type of your date column?
vaibhav
Highlighted
Eight Stars

Re: [resolved] Unparseable date

@Berin Richard: Thanks for the format but I am not specifying the date format in the tMap component. Also the +01:00 is the timezone offset that is provided in the value that I cannot ignore.
@Vaibhav: I have an XSD which has defined the datatype for this field as Date.
So I need to set the date pattern in the XML file's Schema. I don't perform the conversion in tMap which would have been easier for me. So basically the date pattern is defined in the schema available in the Metadata->File xml.
Highlighted
Four Stars

Re: [resolved] Unparseable date

Hi Ketan,
Check this,
http://stackoverflow.com/questions/2375222/java-simpledateformat-for-time-zone-with-a-colon-seperato...
You can get the timezone offset formatted like +01:00 with the SimpleDateFormat in Java 7 (yyyy-MM-dd'T'hh:mm:ss.SSSXXX), or with the Joda's DateTimeFormat (yyyy-MM-dd'T'hh:mm:ss.SSSZZ)
It may help.
Thanks
Vaibhav

View solution in original post

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog