[resolved] Filtering Date Ranges using Expression Builder in tMap

[Anonymous]

Hi All,
I have formulated a filtering date range (inclusive of given dates, date from and date to) inside expression builder in tMap and fortunately, it works!
But, is there any other way to do such date range filtering in TOS? What I have formulated looks like an old school for me

( TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.DateUpdated)).after(TalendDate.parseDate("yyyy-MM-dd",context.DateFrom)) || 
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.DateUpdated)).equals(TalendDate.parseDate("yyyy-MM-dd",context.DateFrom)) ) &&
( TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.DateUpdated)).before(TalendDate.parseDate("yyyy-MM-dd",context.DateTo)) || 
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.DateUpdated)).equals(TalendDate.parseDate("yyyy-MM-dd",context.DateTo)))

Regards,

[Anonymous]

ooohhh I just saw this post a while ago https://community.talend.com/t5/Design-and-Development/a-simple-test-on-Talendate-need-some-java-hel...
Tnx again to Alevy

[alevy]

My pleasure
You might have already cleaned up your expression but why convert your row2.DateUpdated from a date to a string (formatDate) and then back to a date (parseDate)? Also, can you not define your context.DateFrom and context.DateTo as dates rather than strings so they do not need to be parsed to dates in your expression? That would leave you with:

!row2.DateUpdated.before(context.DateFrom) && !row2.DateUpdated.after(context.DateTo)

So much nicer

[Anonymous]

Hi Alevy,
Thanks for the suggestion, but I need to format the row2.DateUpdated in order to remove the time since it returns datetime data type.
For defining my context variables, the value of these will come from outside( property file) so this will be extracted as strings.
Regards,

[Anonymous]

Hi again Alevy,
Could you please explain why !before and !after are equal with <= and >= ? As I understand, ! = not or negation right? Still wondering

[alevy · Accepted solution]

If a date is !before (i.e. not before), then it must be on or after (>= ). Similarly, if the date is !after then it is <=.
So if your row2.DateUpdated includes actual time values (not just always 00:00:00) then you still don't need to truncate it for the context.DateFrom comparison because it does not matter what time the update was on the test date - it will still be on or after 12am on the test date. You will, however, need to remove the time value for the context.DateTo comparison or updates on that date will be excluded. Alternatively, you could add a day to the context.DateTo when it's read in and then compare row2.DateUpdated.before(adjusted context.DateTo).
You can load date contexts from a file as long as (for now) they are in the format "yyyy-MM-dd HH:mm:ss" (see https://community.talend.com/t5/Archive/select-the-first-fille-in-a-folder/td-p/174462). Even if you have to read the dates as strings, I would suggest that you use a tJava to convert the strings to dates so that they do not need to be parsed repeatedly in tMap.

[Anonymous]

You enlighted us once again Tnx Alevy

[alevy]

You can load date contexts from a file as long as (for now) they are in the format "yyyy-MM-dd HH:mm:ss" (see bug).

Just to clarify: the above was incorrect as I misunderstood how loading date context values is supposed to work. In fact, the pattern to apply by tContextLoad in parsing the date string is defined with the context variable's default value. The string passed to tContextLoad should be just the date in the defined format (not including the pattern itself).