How to apply a filter on date fields in UltraEdit

Question

I am trying to use the filter option in UltraEdit. I have a simple CSV file. It has 3 columns. The last column has dates. I need to apply a filter so that I see only those lines with dates older than, say, 05/01/2019.

24.59,000000024.13,06/01/2019
18.45,000000000.00,07/01/2019
18.45,000000018.36,07/01/2019
94.08,000000000.00,02/01/2019
19.83,000000000.00,02/01/2019
77.35,000000000.00,02/01/2019
51.22,000000000.00,02/01/2019
14.02,000000000.00,02/01/2019
70.21,000000000.00,02/01/2019
25.24,000000000.00,02/01/2019
17.35,000000000.00,02/01/2019

Is there a way to do that in UltraEdit in column-mode or not?

Answer 1

This is possible by moving caret to top of file with Ctrl+Home and opening regular Find window for example with Alt+F3 (default key assignment) and

• enter as search string (?:19\d\d|200[0-9]|201[0-8]|0[1-4]/01/2019)$,
• select Current file for option In,
• click on gearwheel button to show advanced find options if not already visible,
• check the option Regular expressions and select Perl,
• check the option Filter lines and select Show,
• check the option Close after find,
• uncheck all other check boy options,
• click on button Next to run the find and hide all lines matching the Perl regular expression search string.

The Perl regular expression search string matches the years 1900 to 1999 OR the years 2000 to 2009 OR the years 2010 to 2018 OR the dates 01/01/2019 to 04/01/2019 which must be found at end of a line for a positive match.

Well, it is unclear if the date format is DD/MM/YYYY (British) or MM/DD/YYYY (U.S.). So the line hiding can be correct or wrong depending on date format and what is meant with older than 05/01/2019. I assume the format DD/MM/YYYY as today is 2019-05-05 (international date format YYYY-MM-DD) and so 06/01/2019 would be a date in future on being MM/DD/YYYY.

The same can be also achieved by searching with Perl regular expression for

(?:0[5-9]/01/2019|[1-3]\d/\d\d/2019|\d\d/0[2-9]/2019|\d\d/1[0-2]/2019)$

matching the dates 05/01/2019 to 09/01/2019 OR any date with first digit of day being 1 to 3 in year 2019 OR any date with month 02 to 09 in year 2019 OR any date with month 10 to 12 in year 2019 found at end of line with using Hide instead of Show as filter option for Filter lines.