[resolved] Convert date and empty date

One Star

[resolved] Convert date and empty date

Hi,
I have a problem in a tMap.
I read a RSS feed with pubdate in GMT or CEST Time.
My colum table is a DATETIME type.
1. How to convert GMT to DATETIME ?
2. CEST is not read by tRSSInput. So, How to insert datetime.now if pubdate is empty ?
Thanks in advance.
Regards

Accepted Solutions
Seven Stars

Re: [resolved] Convert date and empty date

There are a number of problems with your expressions:
1. parseDate converts a String to a Date i.e. the second parameter of parseDate must be a date string as you have correctly used it in your last post. TalendDate.getCurrentDate(), however, already returns a Date so does not need to have parseDate applied to it.
2. In any case, since it is a function, getCurrentDate should not be enclosed in quotes otherwise you are trying to parse an actual String containing the words "TalendDate.getCurrentDate()".
3. If you actually wanted the current date as a String, you could use e.g. TalendDate.getDate("yyyy-MM-dd HH:mm:ss")
4. From the error, it appears that row1.PUBDATE contains the String "20 Jul 2011 11:50:00 GMT", which obviously cannot be parsed to a Long since it is not a whole number. You should simply be parsing it directly to a Date.
i.e.
row1.PUBDATE.isEmpty()
? TalendDate.getCurrentDate()
: TalendDate.parseDate("dd MMM yyyy HH:mm:ss zzz",row1.PUBDATE)

All Replies
One Star

Re: [resolved] Convert date and empty date

Nobody can help me ?
One Star

Re: [resolved] Convert date and empty date

For the empty date I tried this :
(row1.PUBDATE.isEmpty())
? TalendDate.parseDate("yyyy-MM-dd HH:mm:ss","TalendDate.getCurrentDate()")
:row1.PUBDATE
but don't working Smiley Sad
One Star

Re: [resolved] Convert date and empty date

I tried this :
(row1.PUBDATE.isEmpty())
? TalendDate.parseDate("yyyy-MM-dd HH:mm:ss","2011-07-30 12:51:12")
: new java.util.Date(Long.parseLong(row1.PUBDATE))
But when the job is on the first GMT date, the error is :
Exception in component tMap_1
java.lang.NumberFormatException: For input string: "20 Jul 2011 11:50:00 GMT"
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Long.parseLong(Unknown Source)
at java.lang.Long.parseLong(Unknown Source)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_1Process(RecupFlux.java:1246)
at mediascit.recupflux_0_1.RecupFlux.runJobInTOS(RecupFlux.java:1708)
at mediascit.recupflux_0_1.RecupFlux.main(RecupFlux.java:1579)
And : How can I have the currentdate in place of "2011-07-30 12:51:12" ? getCurrentDate don't work Smiley Sad
Seven Stars

Re: [resolved] Convert date and empty date

There are a number of problems with your expressions:
1. parseDate converts a String to a Date i.e. the second parameter of parseDate must be a date string as you have correctly used it in your last post. TalendDate.getCurrentDate(), however, already returns a Date so does not need to have parseDate applied to it.
2. In any case, since it is a function, getCurrentDate should not be enclosed in quotes otherwise you are trying to parse an actual String containing the words "TalendDate.getCurrentDate()".
3. If you actually wanted the current date as a String, you could use e.g. TalendDate.getDate("yyyy-MM-dd HH:mm:ss")
4. From the error, it appears that row1.PUBDATE contains the String "20 Jul 2011 11:50:00 GMT", which obviously cannot be parsed to a Long since it is not a whole number. You should simply be parsing it directly to a Date.
i.e.
row1.PUBDATE.isEmpty()
? TalendDate.getCurrentDate()
: TalendDate.parseDate("dd MMM yyyy HH:mm:ss zzz",row1.PUBDATE)
One Star

Re: [resolved] Convert date and empty date

Hi,
I resolved my problem last night.
It's the good solution.
Thanks.
One Star

Re: [resolved] Convert date and empty date

I discovered a bug :
My job working in my Studio but don't work in production after publication.
Error : Data truncation: Incorrect datetime value: '20 Jul 2011 23:00:00 GMT' for column 'date' at row 1
Error : Data truncation: Incorrect datetime value: '21 Jul 2011 08:37:35 GMT' for column 'date' at row 1
Error : Data truncation: Incorrect datetime value: '21 Jul 2011 07:18:00 GMT' for column 'date' at row 1
The database and tables are the same ...
Any idea ?
Seven Stars

Re: [resolved] Convert date and empty date

It looks like you're trying to pass the original row1.PUBDATE String to the database without parsing it to a Date.
One Star

Re: [resolved] Convert date and empty date

(row1.PUBDATE.isEmpty())
? TalendDate.currentDate()
: TalendDate.parseDate("dd MMM yyyy HH:mm:ss",row1.PUBDATE.substring(0,row1.PUBDATE.indexOf("G")-1))
The currentDate() return a datetime or a string ???
My database column is DATETIME format.
One Star

Re: [resolved] Convert date and empty date

Back from holidays ...
My function :
(row7.PUBDATE.isEmpty())
? TalendDate.currentDate()
: TalendDate.parseDate("dd MMM yyyy HH:mm:ss",row7.PUBDATE.substring(0,row7.PUBDATE.indexOf("G")-1))
Error message :
La méthode currentDate() est indéfinie pour le type TalendDate
Any idea ?
Thanks in advance
Seven Stars

Re: [resolved] Convert date and empty date

It should be TalendDate.getCurrentDate()
One Star

Re: [resolved] Convert date and empty date

Thanks but there is a bug afer few lines :
Exception in component tMap_3
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "26 Aug 2011 08:06:00"
at routines.TalendDate.parseDate(TalendDate.java:503)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_6Process(RecupFlux.java:2148)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_5Process(RecupFlux.java:1062)
at mediascit.recupflux_0_1.RecupFlux.runJobInTOS(RecupFlux.java:4332)
at mediascit.recupflux_0_1.RecupFlux.main(RecupFlux.java:4203)
Caused by: java.text.ParseException: Unparseable date: "26 Aug 2011 08:06:00"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:501)
... 4 more
Seven Stars

Re: [resolved] Convert date and empty date

If you're using TalendDate.parseDate("dd MMM yyyy HH:mm:ss","26 Aug 2011 08:06:00") you should not have a problem. Click on the Code tab at the bottom left of your job-design and find the exact line referred to in the error (2148 from above) to double-check the source of the problem.
One Star

Re: [resolved] Convert date and empty date

Hi, sorry, don't understood all ...
I'm going to code tab :
if (outTMP.source == null) {
pstmt_tMysqlOutput_7.setNull(5,
java.sql.Types.VARCHAR);
} else {
pstmt_tMysqlOutput_7.setString(5,
outTMP.source);
}
2148 line is outTMP.source);
So ?
Seven Stars

Re: [resolved] Convert date and empty date

Well, that can't be right. Run the RecupFlux job to make sure you've got the correct line reference and then go to the Code tab again. What version are you using?
One Star

Re: [resolved] Convert date and empty date

Hi, sorry to be late. Meetings ...
My Version : TIS 4.1.2.r53616
I runed the job again. Error (the same) :
connecting to socket on port 3488
connected
Exception in component tMap_3
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "30 Aug 2011 07:06:00"
at routines.TalendDate.parseDate(TalendDate.java:503)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_6Process(RecupFlux.java:2148)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_5Process(RecupFlux.java:1062)
at mediascit.recupflux_0_1.RecupFlux.runJobInTOS(RecupFlux.java:4332)
at mediascit.recupflux_0_1.RecupFlux.main(RecupFlux.java:4203)
Caused by: java.text.ParseException: Unparseable date: "30 Aug 2011 07:06:00"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:501)
... 4 more
disconnected
Job RecupFlux terminé à 10:33 30/08/2011.
The code :
outTMP_tmp.date_insertion = (row7.PUBDATE
.isEmpty()) ? TalendDate
.getCurrentDate()
: TalendDate
.parseDate(
"dd MMM yyyy HH:mm:ss",
row7.PUBDATE
.substring(
0,
row7.PUBDATE
.indexOf("G") - 1));
outTMP_tmp.id_statut = null;
outTMP_tmp.note = null;
outTMP_tmp.etat = "0";
outTMP = outTMP_tmp;
The line 2148 is the ".parseDate(
Community Manager

Re: [resolved] Convert date and empty date

Hi Scots
Exception in component tMap_3
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "30 Aug 2011 07:06:00"

It seems you do not come from a English speaking country, try to use the method
TalendDate.parseDateLocale("dd MMM yyyy HH:mm:ss", "30 Aug 2011 07:06:00","EN")
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Convert date and empty date

Yes, I'm in France Smiley Happy
Good job Shong, it works Smiley Happy
I try to publish my job on my server.