topic Re: CHANGE DATE FORMAT WITH BLANK ROWS in Talend Studio

CHANGE DATE FORMAT WITH BLANK ROWS

Anonymous — Mon, 23 Sep 2019 09:57:03 GMT

Hi,

I am trying to convert a string that acts as an input with date format "EEE MMM dd HH:mm:ss Z yyyy" into the format "dd/MM/yyyy", avoiding the existing blank rows. I found the following command to do it:

TalendDate.isDate(row1.Fecha_de_Llegada_Real,"EEE MMM dd HH:mm:ss Z yyyy")? TalendDate.parseDate("dd/MM/yyyy",row1.Fecha_de_Llegada_Real) : null

Although I am 100% sure this is the correct input format, as when printing this row as a string I obtain strings like "Wed Jan 02 00:00:00 CET 2019", I get all null because all the values of the TalendDate.isDate() give a false value.

Any idea why this is heppening?

Thank you so much

Re: CHANGE DATE FORMAT WITH BLANK ROWS

Anonymous — Mon, 23 Sep 2019 13:43:50 GMT

There were a couple of issues in this. The way to get what you want is shown below....

TalendDate.isDate(row1.Fecha_de_Llegada_Real,"EEE MMM dd HH:mm:ss z yyyy")? TalendDate.formatDate("dd/MM/yyyy", TalendDate.parseDate("EEE MMM dd HH:mm:ss z yyyy",row1.Fecha_de_Llegada_Real)) : null

The first error was the format. You used Z instead of z.

There was another omission though. In order to change the output format, you need to first convert the String to a Date. THEN you can format it to "dd/MM/yyyy".

The above code should work for you.

Re: CHANGE DATE FORMAT WITH BLANK ROWS

Anonymous — Mon, 23 Sep 2019 15:01:12 GMT

Thanks for the reply. You are right when changing the output format.

Unfortunately, the first change didn't work as expected. Changing the capital z did not make any change to the result of getting all false. Actually, I found some strange error when trying to parse the date as when using:

TalendDate.parseDateLocale("EEE MMM dd HH:mm:ss z yyyy",row1.Fecha_de_Llegada_Real,"EN"), it works perfectly until it finds a null, obviously showing a NullPointerException, while using:

TalendDate.parseDate("EEE MMM dd HH:mm:ss z yyyy",row1.Fecha_de_Llegada_Real), it says that it is an Unparseable date, and using your proposed expression:

TalendDate.formatDate("dd/MM/yyyy", TalendDate.parseDate(
"EEE MMM dd HH:mm:ss z yyyy", row1.Fecha_de_Llegada_Real)), it

appears a mismatch error saying that "cannot convert from String to Date", I guess as a consequence of the previous error.

I know it does not make sense, that's why my confusion.

Re: CHANGE DATE FORMAT WITH BLANK ROWS

Anonymous — Mon, 23 Sep 2019 15:52:51 GMT

Ah, I see. Sorry I neglected to include a check for a null input. You need to use this....

row1.Fecha_de_Llegada_Real!=null ?(TalendDate.isDate(row1.Fecha_de_Llegada_Real,"EEE MMM dd HH:mm:ss z yyyy")? TalendDate.formatDate("dd/MM/yyyy", TalendDate.parseDate("EEE MMM dd HH:mm:ss z yyyy",row1.Fecha_de_Llegada_Real)) : null) : null

I've simply added a check for the input value (or lack of). If your data comes in as null, you cannot perform String to Date conversion since null is nothing (not a String). The above should work.

The Z to z is the difference between.....

`z`	Time zone	General time zone	`Pacific Standard Time`; `PST`; `GMT-08:00`
`Z`	Time zone	RFC 822 time zone	`-0800`

Re: CHANGE DATE FORMAT WITH BLANK ROWS

Anonymous — Mon, 23 Sep 2019 16:19:57 GMT

In the end it was easier than what we thought, but thanks to the beginning of your last code was the key, as by using:

row1.Fecha_de_Llegada_Real!=null ? TalendDate.parseDateLocale("EEE MMM dd HH:mm:ss z yyyy",row1.Fecha_de_Llegada_Real,"EN") : null, worked perfectly.

Thank you so much!

Re: CHANGE DATE FORMAT WITH BLANK ROWS

Anonymous — Mon, 23 Sep 2019 16:23:32 GMT

I'm glad that works, but that will not change the format, that converts your Sting into a Date. I suspect your output column is a Date type and by default has the correct String output format. It is important to recognise the difference here 🙂