Change Due Date with Ticket Rule
I want to change the Due Date when a ticket is saved to add 2 weeks from the creation date. I currently have it setting the Due Date to the Creation Date, but can’t figure out how to add 2 weeks (14 days) to it.
update HD_TICKET as T
set T.DUE_DATE = T.CREATED
where
(T.ID in (<TICKET_IDS>))
What is needed to add those dates?
update HD_TICKET as T
set T.DUE_DATE = T.CREATED
where
(T.ID in (<TICKET_IDS>))
What is needed to add those dates?
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
airwolf
14 years ago
Use the DATE_ADD() function in MySQL:
update HD_TICKET as T
set T.DUE_DATE = DATE_ADD(T.CREATED, INTERVAL 14 DAY)
where
(T.ID in (<TICKET_IDS>))
update HD_TICKET as T
set T.DUE_DATE = DATE_ADD(T.CREATED, INTERVAL 14 DAY)
where
(T.ID in (<TICKET_IDS>))
Posted by:
GillySpy
14 years ago
This will work great.
Just nitpicking perhaps and if your not concerned about the time portion then this doesn't matter at all.
Keep in mind that the DUE_DATE field is a "DATE" field and CREATED is a TIMESTAMP field. After this update the time portion will be dropped which is effectively setting the DUE_DATE to the beginning of that day. Where I could see this coming into play is if you were expecting to compare DUE_DATE to a timestamp field you might not get the results you thought. This is because, the time portion for DATE field is implicitly midnight (00:00:00.000) For example, if a ticket was created at the end of the business day, when you add 14 days you will be 14days ahead but will be at the beginning of the 14th day rather than the end. This could cause the due date to come early.
In terms of SLA this will likely not hurt you by making some tickets come due "early".
Again, if your not concerned about the time portion or tickets coming due up to 1 business day early then just ignore what I said
Just nitpicking perhaps and if your not concerned about the time portion then this doesn't matter at all.
Keep in mind that the DUE_DATE field is a "DATE" field and CREATED is a TIMESTAMP field. After this update the time portion will be dropped which is effectively setting the DUE_DATE to the beginning of that day. Where I could see this coming into play is if you were expecting to compare DUE_DATE to a timestamp field you might not get the results you thought. This is because, the time portion for DATE field is implicitly midnight (00:00:00.000) For example, if a ticket was created at the end of the business day, when you add 14 days you will be 14days ahead but will be at the beginning of the 14th day rather than the end. This could cause the due date to come early.
In terms of SLA this will likely not hurt you by making some tickets come due "early".
Again, if your not concerned about the time portion or tickets coming due up to 1 business day early then just ignore what I said
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
