topic Re: [resolved] Unparseable Date with date format dd-Mon-yyyy in Talend Studio

[resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Sat, 16 Nov 2024 11:35:25 GMT

Hello,
This might be stupid but I am struggling with this issue for 2 hours now.
csv file contains
Date, Bid, Offer, Mid
10-Apr-2014, 21.400, 21.450, 21.4250
and I am trying to load the date into a PostgresQL date column to have "2014-04-10"
whatever talend parse date pattern I use I keep on having this exception !!!

Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "10-Apr-2014"
at routines.TalendDate.parseDate(TalendDate.java:836)
at nitrofer.load_dayahead_0_1.Load_DayAhead.tFileInputDelimited_1Process(Load_DayAhead.java:2156)
at nitrofer.load_dayahead_0_1.Load_DayAhead.tFileList_1Process(Load_DayAhead.java:1126)
at nitrofer.load_dayahead_0_1.Load_DayAhead.tFileInputProperties_1Process(Load_DayAhead.java:515)
at nitrofer.load_dayahead_0_1.Load_DayAhead.runJobInTOS(Load_DayAhead.java:3030)
at nitrofer.load_dayahead_0_1.Load_DayAhead.main(Load_DayAhead.java:2862)
Caused by: java.text.ParseException: Unparseable date: "10-Apr-2014"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:822)
... 5 more
So please any ideas to have this solved ?
Thanks,
LR

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

G_ — Wed, 09 Jul 2014 17:55:19 GMT

Hi,
Please use the below condition

TalendDate.formatDate("yyyy-MM-dd",your date field);
Thanks
Ganesh

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Wed, 09 Jul 2014 18:41:23 GMT

Ganesh,
Tried your idea: and it is unsuccessful.
Here's my tMap setup:
Column Type Format Type
------------------------------------------------------------------------------------------
row2.Date Date "dd-MMM-yyyy" ==> Mapped to Date Expression:TalendDate.formatDate("yyyy-MM-dd",row2.Date)
==> Type mismatch: cannot convert from String to Date
I don't even understand where this String comes from !
Any other ideas ?
Thanks
LR

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Wed, 09 Jul 2014 19:25:38 GMT

At first a pattern is only needed to transform a textual representation of a date into a java Date object which can without any patterns send to a database.
It looks like you have configured the row2.Date column already as Date type and for the text conversion the given pattern will be used (dd-MMM-yyyy). Now you have an date typed object but you do not need the expression anymore.
PostgreSQL works just fine with the date object.
I would say, simply remove the expression and set only the column row2.Date as value, thats it.
A database column of type date, timestamp and so on does not have a pattern. Talend sets automatically a pattern for all date typed columns in the schema but this has no effect, only if a automatic type conversion is needed (e.g. from String to Date) the pattern will be used.

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

G_ — Wed, 09 Jul 2014 20:08:11 GMT

Hi jlolling,
If he passes data as it is, he will get the output as year as 0014 instead of 2014. So i think we need to change the format for the year.Not sure how to do that.
Irako,
Try to do like this,
Go to tmap out put flow, pass row2.Date instead of TalendDate.formatDate("yyyy-MM-dd",row2.Date).
At the bottom of the tmap output flow try to change your datepattern as "yyyy-MM-dd". If you got 2014-04-10 then we are good.
I think you may face issue on year. Please let me know what you got.
Thanks,
Ganesh

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Wed, 09 Jul 2014 20:10:24 GMT

So you suggest the following setup:
Column Type Format Type Format
------------------------------------------------------------------------------------------------------------
row2.Date Date "dd-MMM-yyyy" ==> Mapped to Date row2.Date yyyy-MM-dd
and Talend replies, in red
Unparseable date: "10-Apr-2014"
That was my very first thought : no conversion needed from Date to Date ! But it really looks like this format dd-MMM-yyyy has an issue. Maybe be the month capitalized ?

I tried this in PostgreSQL

CREATE TABLE x
(
a date
) ;
insert into x values ('10-Apr-2014') ;

==> successful
select * from x ;
=>"2014-04-10"
From PostgreSQL side, it works fine.

I am open to workarounds now , because I really can't get the logic.
Thanks,
LR

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Wed, 09 Jul 2014 22:41:37 GMT

I could not get a solution using TalendDate routine, formatDate nor parseDate.
All date formats lead me to this "Unparseable Date" exception.
As a workaroud, I will add an additional column of varchar type. And in the input flow of tMap, set the row2.Date as String type.
I will then map the row2.Date to the new column and perform the Date casting at database level using to_timestamp(new column, 'DD-Mon-YYYY') function. Notice the month part of date pattern from PostgreSQL (same as Oracle).
Thanks for your updates.
LR

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Thu, 10 Jul 2014 08:32:28 GMT

hi,
please have a look at dedicated talend help page :
https://help.talend.com/search/all?query=TalendDate+Routines&content-lang=en
you'll find documentation&example for each date Talend function
regards
laurent

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Tue, 25 Nov 2014 02:13:59 GMT

I have the same issue. Recieve a date field from oracle. Try to output it with a date pattern and receive unparesable date.
Talend seems to want to parse milliseconds when they aren't available on the standard date type.
Execption in component: tParseRecordSet_1
Unparseable date: "2014-12-30 00:00:00.0"
I am using 5.5.1 and the only way I can get this to work is cast dates to string in oracle.
Seems like a lot of unnecessary type converting.

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Tue, 25 Nov 2014 16:38:58 GMT

hi,


Unparseable date: "2014-12-30 00:00:00.0"

And what is the pattern you're using ?
regards
laurent

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Mon, 28 Dec 2015 11:21:47 GMT

Need to change date containing milliseconds to seconds, truncating milliseconds data without upcasting. Have used the formula
(row2.START_DATE==null || row2.START_DATE.isEmpty())? null : TalendDate.parseDate("yyyy/MM/dd'T'hh:mm:ss",TalendDate.formatDate("dd/MM/yyyyhh:mm:ss",TalendDate.parseDate("yyyy/MM/dd'T'hh:mm:ss",row2.START_DATE))) in the tmap component.
But, it is showing the following error..
Caused by: java.text.ParseException: Unparseable date: "2015-06-12T20:40:07"

Re: [resolved] Unparseable Date with date format dd-Mon-yyyy

Anonymous — Mon, 28 Dec 2015 14:43:53 GMT

Thank you, done somehow. have used tbufferinput in middle, got the output