I'm trying to configure rules using the advanced config (HQL).
This is what I had before and it was working as intended:
a.employeeType= 'Employee' AND a.startdate < DATE_ADD(NOW(), INTERVAL 14 DAY) AND (a.enddate > NOW() OR a.enddate is null)
After our customer updated the environment it stopped functioning as SQL functioned stopped working there.
I was redirected here on how to do this config with HQL and asked to use the current_date - functionality.
Ex. where inv.dueDate < = current_date - 15
I had some issues with this and after doing some testing I noticed that using this query:
select current_date-15 I got this as the result:
Now this is an issue since there aren't that many (2023.07.89) days in a month so this can't be used reliably in the advanced config.
Use the below sample code snippet:
datediff (curdate(), a.startdate) between 0 and 14
Your advanced query should look something like below:
a.employeeType= 'Employee' AND datediff (curdate(), a.startdate) between 0 and 14 AND (a.enddate > NOW() OR a.enddate is null)
Let me know if this helps!
a.statuskey = 1 and datediff (curdate(), a.startdate) between 0 and 15