topic Re: [resolved] Convert date and empty date in Talend Studio

[resolved] Convert date and empty date

Anonymous — Sat, 16 Nov 2024 12:47:39 GMT

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

Re: [resolved] Convert date and empty date

Anonymous — Wed, 20 Jul 2011 11:46:13 GMT

Nobody can help me ?

Re: [resolved] Convert date and empty date

Anonymous — Wed, 20 Jul 2011 12:34:23 GMT

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

Re: [resolved] Convert date and empty date

Anonymous — Wed, 20 Jul 2011 13:02:38 GMT

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

Re: [resolved] Convert date and empty date

alevy — Thu, 21 Jul 2011 01:48:29 GMT

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)

Re: [resolved] Convert date and empty date

Anonymous — Thu, 21 Jul 2011 08:55:29 GMT

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

Re: [resolved] Convert date and empty date

Anonymous — Thu, 21 Jul 2011 10:01:39 GMT

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 ?

Re: [resolved] Convert date and empty date

alevy — Thu, 21 Jul 2011 10:05:07 GMT

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

Re: [resolved] Convert date and empty date

Anonymous — Fri, 22 Jul 2011 10:51:08 GMT

(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.

Re: [resolved] Convert date and empty date

Anonymous — Thu, 25 Aug 2011 15:29:34 GMT

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

Re: [resolved] Convert date and empty date

alevy — Fri, 26 Aug 2011 00:32:45 GMT

It should be TalendDate.getCurrentDate()

Re: [resolved] Convert date and empty date

Anonymous — Fri, 26 Aug 2011 09:33:18 GMT

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

Re: [resolved] Convert date and empty date

alevy — Sat, 27 Aug 2011 06:25:30 GMT

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.

Re: [resolved] Convert date and empty date

Anonymous — Sat, 27 Aug 2011 10:45:58 GMT

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 ?

Re: [resolved] Convert date and empty date

alevy — Sat, 27 Aug 2011 12:10:17 GMT

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?

Re: [resolved] Convert date and empty date

Anonymous — Tue, 30 Aug 2011 09:36:18 GMT

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(

Re: [resolved] Convert date and empty date

Anonymous — Tue, 30 Aug 2011 09:45:01 GMT

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

Re: [resolved] Convert date and empty date

Anonymous — Tue, 30 Aug 2011 10:25:00 GMT

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