Campaign Classic V7 - The “after” operator on a date field in Query Component is not returning correct results.

Description

The “after” operator on a date field in Query Component is not returning correct results. The query is still returning records where creation date = max creation date. What is the issue?

Steps to reproduce:

- Go to any schema
- Go to the data tab
- Configure the list and add the Creation date field
- Sort the list by Creation date in descending order, note the max creation date & time
- Create a new workflow
- Add a query component to the workflow
- update the query to have a filter where Creation date “after” max creation date (noted from the previous step)
- Ideally, the query should not return any records but still, it will return the records where creation date = max creation date

Resolution

Cause

The query in the workflow is working on the format “hh:mm:ss.000” which is in microseconds.

This means that in this table the maximum date is 4.38.56 seconds but there are 6 unique records that have the maximum date as 4 hours, 38 min, 56 seconds, and further split like a (001 microseconds /002 microseconds  /003 microseconds  /004 microseconds  /005  microseconds  / 006 microseconds).

That is why it fetches the records.

Test by putting a query as ‘2017-10-18 16:38:57 PM’ in the workflow” again. This will not fetch any result. This certifies that there are multiple records with the date 16:38:56 seconds as explained above.

Resolution

We can amend the workflow by adding 1 second to the maximum and that should solve the problem.

On this page