topic Re: [resolved] WRONG OUTPUT IN COMPARE DATE in Talend Studio

[resolved] WRONG OUTPUT IN COMPARE DATE

Anonymous — Thu, 05 Nov 2015 18:08:07 GMT

Dear All,
I am working on a job and i don't understand my out data because after many tests everything look good.
I have an input file with a date column and objective is to classify all rows by month
Example:
Input file with date MM/dd/yyyy
MLarronde;10/05/2015;Parrot;Parrot EPM & COMP;Billable;ACCEPTED
MLarronde;10/06/2015;Parrot;Parrot EPM & COMP;Billable;ACCEPTED
MLarronde;10/31/2015;Parrot;Parrot EPM & COMP;Billable;ACCEPTED
MLarronde;10/01/2015;Parrot;Parrot EPM & COMP;Billable;ACCEPTED
svautrot;09/15/2015;Bureau Veritas;Bureau Veritas - LMS ILT;Billable;ACCEPTED
svautrot;09/16/2015;WHO;Testing support;Billable;ACCEPTED
svautrot;09/16/2015;Bureau Veritas;Bureau Veritas - LMS ILT;Billable;ACCEPTED
svautrot;09/01/2015;Bureau Veritas;Bureau Veritas - LMS ILT;Billable;ACCEPTED

With these inputs i get this output
Output file for previous month (October)
MLarronde;10/05/2015;Parrot;Parrot EPM & COMP;Billable;ACCEPTED
MLarronde;10/06/2015;Parrot;Parrot EPM & COMP;Billable;ACCEPTED
Output file for month September

svautrot;09/15/2015;Bureau Veritas;Bureau Veritas - LMS ILT;Billable;ACCEPTED
svautrot;09/16/2015;WHO;Testing support;Billable;ACCEPTED
svautrot;09/16/2015;Bureau Veritas;Bureau Veritas - LMS ILT;Billable;ACCEPTED

Screenshot of my tMap

First i generate 2 variables which are FirstDay_currentDate and LastDay_currentDate
FirstDay_currentDate expression

==> TalendDate.getFirstDayOfMonth(TalendDate.getCurrentDate())
LastDay_currentDate expression

==> TalendDate.getLastDayOfMonth(TalendDate.getCurrentDate())

For October output file, i do this condition:

TalendDate.compareDate(row24.activityDate,TalendDate.addDate(Var.FirstDay_currentDate,-1,"MM")) >= 0 && 
TalendDate.compareDate(row24.activityDate,TalendDate.addDate(Var.LastDay_currentDate,-1,"MM")) <= 0

And for September output file, i do this condition:

TalendDate.compareDate(row24.activityDate,TalendDate.addDate(Var.FirstDay_currentDate,-2,"MM")) >= 0 && 
TalendDate.compareDate(row24.activityDate,TalendDate.addDate(Var.LastDay_currentDate,-2,"MM")) <= 0

Please help me solve this problem, because i don't know why i don't get blue rows
MLarronde;10/31/2015;Parrot;Parrot EPM & COMP;Billable;ACCEPTED
MLarronde;10/01/2015;Parrot;Parrot EPM & COMP;Billable;ACCEPTED
svautrot;09/01/2015;Bureau Veritas;Bureau Veritas - LMS ILT;Billable;ACCEPTED

Thanks,

Re: [resolved] WRONG OUTPUT IN COMPARE DATE

Anonymous — Fri, 06 Nov 2015 09:21:36 GMT

Dear all,
Somebody has solution to suggest to me please?

Thanks,

Re: [resolved] WRONG OUTPUT IN COMPARE DATE

Anonymous — Fri, 06 Nov 2015 09:37:28 GMT

I'm not sure I fully understand what you are doing here, but the first thing I would check would be the TalendDate.compareDate() sections. You are getting the current date and working with that assuming that it will hold just the day information. It will also contain a time element. You are comparing against other dates which I assume may also have time elements. If you compare 01/01/2000 with 01/01/2000, you expect a result of 0 (the same). But if you compare 01/01/2000 12:02 with 01/01/2000 12:01 you will get 1 or 01/01/2000 12:01 with 01/01/2000 12:02 you will get -1. You need to ensure that you beginning of month and end of month dates are exactly the beginning and end....down to the millisecond.

Re: [resolved] WRONG OUTPUT IN COMPARE DATE

Anonymous — Fri, 06 Nov 2015 10:08:48 GMT

Dear rhall_2.0?
Your explanation is right, but in my tMap i define date format MM/dd/yyyy so normally everything should be good.
I attach my job to this post
Thanks,

testDatebool.zip.zip

Re: [resolved] WRONG OUTPUT IN COMPARE DATE

Anonymous — Fri, 06 Nov 2015 10:34:34 GMT

Your start and end dates are defined by getCurrentDate() in your tMap variables. Try something like this for the first day of the month....

TalendDate.getFirstDayOfMonth(TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd", TalendDate.getCurrentDate())))

For the last day of the month you will actually need to set the time to 23:59:59.9999 or set it to the very next day (beginning of the next month) with a time of 00:00:00.0000 and change your logic slightly to account for that.

Re: [resolved] WRONG OUTPUT IN COMPARE DATE

Anonymous — Fri, 06 Nov 2015 16:19:04 GMT

Dear rhall_2.0,
Thank you very much for your solution, i have changed my logic and it works.