Campaign Classic V7 - The “after” operator on a date field in Query Component is not returning correct results.
Description 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
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 resolution
Cause
The query in the workflow is working on the format “hhss.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.
recommendation-more-help
3d58f420-19b5-47a0-a122-5c9dab55ec7f