topic Re: Output Wrong when compare two dates with string format in Tmap in Talend Studio

Output Wrong when compare two dates with string format in Tmap

Anonymous — Fri, 11 May 2018 15:51:12 GMT

Hi,

Can anyone please advise me below issue? Thanks a lot! -

My purpose was to compare if "ETD" is later than "ETA Port" in string data type

I changed my schema to show "string" for two dates, and then I wrote syntax - ETD.compareTo(ETA_to_Discharge_Port)>0

I think my syntax is correct to compare two strings (please correct me if I am wrong) and then I got below output which ETD is earlier than ETA Port

Re: Output Wrong when compare two dates with string format in Tmap

daez — Fri, 11 May 2018 15:54:26 GMT

Hello @szhou1 ,

Quite new here but if I can try to help : Did you try to use TalendDate.compareDate() & FormatDate() ?

Check here : https://help.talend.com/reader/~R4Lk_SlELw9a8pKBKTm9A/JFWl8U3lEbhWZEFAUtYvMw

Cheers

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Fri, 11 May 2018 16:09:05 GMT

Thank you for your help!
I input string data type for two dates so I did not use "compareDate()" syntax because this will need "Date"data type
If I change my input dates to "Date" data type, it will cause issue because my input Excel reports include lot of different date format

Re: Output Wrong when compare two dates with string format in Tmap

vboppudi — Fri, 11 May 2018 16:15:57 GMT

Hi, I think you can not compare to dates in string format. May be you can use variable ports in tMap to convert input string to date temporarily and compare dates. for output you can use input string values.

Regards,

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Fri, 11 May 2018 16:50:25 GMT

Hi,

I changed into "date" data type and used syntax - TalendDate.compareDate(BKGerrors.ETD, BKGerrors.ETA_to_Discharge_Port)>0

and I got below output -

I checked the source data and find that the output is incorrect because the source for ETD should be below

not sure why I got 5/2/2019 which is totally different from the source

Re: Output Wrong when compare two dates with string format in Tmap

manodwhb — Fri, 11 May 2018 17:23:42 GMT

You need to convert string to date both column values in same format.and then you need to compare.

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Fri, 11 May 2018 18:27:23 GMT

Hi,

I created variable to convert string to date and compare. and I got below errors -

Re: Output Wrong when compare two dates with string format in Tmap

vboppudi — Fri, 11 May 2018 19:11:28 GMT

Please try below

From the source 4th record rejected because of ETD>ETA.

Regards,

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Fri, 11 May 2018 19:46:44 GMT

Hi,

Thank you for the help!

Can you please explain in detail why you use tfixrowinput? I am new on Talend so not quite understand...

The flow I build is below

Re: Output Wrong when compare two dates with string format in Tmap

vboppudi — Fri, 11 May 2018 19:53:03 GMT

tFixedFlowInput used to create sample data.

Regards,

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Fri, 11 May 2018 20:04:24 GMT

Thanks!

After I change my syntax to TalendDate.compareDate(Var.var1,Var.var2)>0 I still get below errors -

Re: Output Wrong when compare two dates with string format in Tmap

vboppudi — Fri, 11 May 2018 20:23:07 GMT

please provide tMap configurations.

Regards,

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Fri, 11 May 2018 20:28:17 GMT

Hi,

I filtered out the empty data and I don't have issue for parse date error for now.

but I have other issue - the output I want is data with ETD>ETA

For now no matter what I change equation I got ETD<ETA (var1 is ETA and var2 is ETD)

Re: Output Wrong when compare two dates with string format in Tmap

vboppudi — Fri, 11 May 2018 20:36:21 GMT

Try the following:

TalendDate.compareDate(Var.ETA_Date,Var.ETD_Date)<0

@rchinta date1 (Var.ETA_Date)
@rchinta date2 (Var.ETD_Date)
@return the result wheather two date is the same,

if first one less than second one return number -1 (Var.ETA_Date<Var.ETD_Date)

equal's return number 0 (Var.ETA_Date=Var.ETD_Date)

bigger than return number 1. (Var.ETA_Date>Var.ETD_Date)

Regards,

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Fri, 11 May 2018 20:52:50 GMT

Sorry not understand where I need to change - I think I use same thing you just advised

Re: Output Wrong when compare two dates with string format in Tmap

manodwhb — Mon, 14 May 2018 09:18:51 GMT

@szhou1,you were getting empty value in date coumn,since you were not able to convert.

can you cehck with below expressions in Variable.

"".equals(row1.EDT) || row1.EDT == null ? "" : TalendDate.parseDate("mm/DD/yyyy",row1.EDT))
"".equals(row1.ETA) || row1.ETA == null ? "" : TalendDate.parseDate("mm/DD/yyyy",row1.ETA))

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Mon, 21 May 2018 16:53:43 GMT

Hi,

I filtered out empty value but issue is still there -
i.e. when compare 12/31/2017 with 1/4/2018 Talend will show 12/31/2017>1/4/2018

Re: Output Wrong when compare two dates with string format in Tmap

Jesperrekuh — Mon, 21 May 2018 23:12:37 GMT

its NOT TalendDate.parseDate("mm/DD/yyyy",row1.ETA))

But "MM/dd/yyyy"

mm=minutes

MM=Month

Re: Output Wrong when compare two dates with string format in Tmap

Anonymous — Tue, 22 May 2018 14:28:45 GMT

Hi,

I think I solved this problem. Thanks Guys!