Using the Correct Operators to Search for Dates or Times in Agiloft

Even if a date field of a record says “3/12/2018,” the information saved in the database also includes a specific minute and second.

This means that you should never use the equals (=) operator when creating a search that filters based on date or time. By using the equals (=) operator, you are instructing the system to look for a specific combination of date, minute, and time within the specified field. For example, consider the following search:

If you ran this search on March 16, 2018 at 5:05:34 pm, the system would only find a match if a record had been created on March 16, 2018 at 12:05:34 pm exactly. As you might imagine, this is unlikely to give you the desired results.

Luckily, the solution is simple. Instead of using the equals (=) operator, you should always use the less than or equals (<=), less than (<), greater than or equals (>=), and greater than (>) operators to create two criteria that frame the day or time you are searching for. In the scenario above, you might use the following search:

If you ran this search on March 16, 2018 at 5:05:34 pm, the system would find all records that were created on March 16, 2018 between 11:05:35 am and 12:05:34 pm.

For more detailed information, here is a visual timeline and a table that describes the search behavior of time-based searches.

Operator Operator Symbol Condition Matching Values
is equal to = four hours ago 10:00:00
is equal to = four hours in the future 18:00:00
is not equal to != four hours ago …-9:59:59 AND 10:00:01-…
is not equal to != four hours in the future …-17:59:59 AND 18:00:01-…
is greater than or equals >= four hours in the ago …-10:00:00
is greater than or equals >= four hours in the future 18:00:00-…
is greater than > four hours ago …-9:59:59
is greater than > four hours in the future 18:00:01-…
is less than or equals <= four hours ago 10:00:00-14:00:00(now)
is less than or equals <= four hours in the future 14:00:00(now)-18:00:00
is less than < four hours ago 10:00:01-14:00:00(now)
is less than < four hours in the future 14:00:00(now)-17:59:59

When searching by “0 hours old” or “0 hours in the future” the search behaves slightly differently:

Operator Operator Symbol Condition Matching Values
is greater than or equals >= 0 hours ago …-14:00:00(now)
is greater than or equals >= 0 hours in the future 14:00:00(now)-…
is greater than > 0 hours ago …-13:59:59
is greater than > 0 hours in the future 14:00:01-…
is less than or equals <= 0 hours ago 14:00:00
is less than or equals <= 0 hours in the future 14:00:00
is less than < 0 hours ago None
is less than < 0 hours in the future None