/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


How do you subtract days in a field from a date to create another date

03/26/2020 198 views

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!

0 Comments   [ + ] Show comments

Comments


All Answers

0

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())





Answered 03/27/2020 by: chucksteel
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ