Output Wrong when compare two dates with string format in Tmap

Six Stars

Output Wrong when compare two dates with string format in Tmap

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

Capture.PNG

Seven Stars

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

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

Six Stars

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

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
Nine Stars

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

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,

Veeru Boppudi
Six Stars

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

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 - 

Capture.PNG

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
Capture.PNG

Thirteen Stars

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

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

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

Hi,

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

Capture.PNG

Capture.PNG

Nine Stars

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

Please try below

DateCompare.PNGDateCompare1.PNG

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

Regards,

Veeru Boppudi
Six Stars

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

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

Capture.PNG

Nine Stars

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

tFixedFlowInput used to create sample data.

 

Regards,

Veeru Boppudi
Six Stars

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

Thanks!

 

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

Capture.PNG

 

Nine Stars

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

please provide tMap configurations.

 

Regards,

Veeru Boppudi
Six Stars

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

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)

Capture.PNG

Nine Stars

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

Try the following:

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

 @param date1 (Var.ETA_Date)
 @param 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,

 

Veeru Boppudi
Six Stars

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

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

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

@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))

Manohar B
Six Stars

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

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
Ten Stars

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

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

 

But "MM/dd/yyyy"  

 

mm=minutes

MM=Month

Six Stars

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

Hi,

I think I solved this problem. Thanks Guys!