Using HQL in the Advanced Rules config

[RoniYla]

Hi,

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.

[naveenss]

Hi @RoniYla 

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!

[rushikeshvartak]

Refer : https://forums.saviynt.com/t5/identity-governance/how-to-configure-technical-rule-with-quot-startdat... 

a.statuskey = 1 and datediff (curdate(), a.startdate) between 0 and 15