How do you subtract days in a field from a date to create another date
In a KACE rule, I'm trying to calculate a Reminder date (value 20, a Date field) by adding a negative number of days (value 21, a Number field) to an Expiration Date (value 18, a date field). I'm using this code, and received the error below. I also tried using 'DAY' instead of 'DD' in the DATEADD command. Any assistance you can provide is appreciated!
update HD_TICKET
set
HD_TICKET.CUSTOM_FIELD_VALUE20 = DATEADD(DD, HD_TICKET.CUSTOM_FIELD_VALUE21, HD_TICKET.CUSTOM_FIELD_VALUE18)
where
(HD_TICKET.ID in (<TICKET_IDS>))
03/26/2020 14:58:52> Starting: 03/26/2020 14:58:52 03/26/2020 14:58:52> Executing Select Query... 03/26/2020 14:58:52> selected 1 rows 03/26/2020 14:58:52> Executing Update Query... 03/26/2020 14:58:52> mysqli error: [1370: execute command denied to user 'B1'@'%' for routine 'ORG1.DATEADD'] in EXECUTE("update HD_TICKET set HD_TICKET.CUSTOM_FIELD_VALUE20 = DATEADD(DD, HD_TICKET.CUSTOM_FIELD_VALUE21, HD_TICKET.CUSTOM_FIELD_VALUE18) where (HD_TICKET.ID in (23615))") 03/26/2020 14:58:52> Ending: 03/26/2020 14:58:52
Also, after I get this working, I will need to create a Daily rule that will compare the Reminder Date to the Current Date and send an email. I could use some help with properly writing the code to compare those two dates. Thank you!
Answers (1)
The function is date_add, not dateadd:
https://mariadb.com/kb/en/date_add/
Or, instead of adding a negative number of days, you can use date_sub:
https://mariadb.com/kb/en/date_sub/
To match tickets where reminder date (CUSTOM_FIELD_VALUE20) is today:
WHERE DATE(HD_TICKET.CUSTOM_FIELD_VALUE20) = DATE(NOW())
