Client Management Suite

 View Only
Back to Library

Auto Close Notification Policy will not pull GetDate() properly 

May 05, 2010 01:44 PM


Hello Everyone, 

I was curious if anyone has encountered a similar issue using the external ID as method for the use of auto closing an incident. I use a query to pull a date into the external Id field for the waiting and resolved status. It will evaluate the if the status is ready to commit an edit to the incidents that match the query results. Up till last week this worked fine but just recently it failed and I have narrowed it down to the following error. Any help on this issue is greatly appreciated.

This query no longer works due to an error generated:
 
Error Generated: Conversion failed when converting datetime from character string.
 
SELECT              
workitem_number AS 'Ticket_Number'        
FROM              
HD_workitem_current_view              
WHERE              
workitem_status_lookup_id in ('100','400')         
andworkitem_external_reference not like ''        
andworkitem_external_reference < Getdate()
 
The GetDate() function does not properly retrieve the current date
  
These are the incident rules to fire with the following configurations:
 


Incident Rule #1:
 
Name: Set close time for resolved status
 
Comment: 1 Business Day
 
Set these properties:
Set "External ID" to "AEXQUERY[[Select dbo.[add_business_Minutes](dbo.HD_WorkItem_detail_view.workitem_modified_on, 540,'9d785fd1-7789-4190-b5e5-0d478f5f15d9','885c5c5c-92b1-4049-8971-5f9c3935c8d9') from dbo.HD_WorkItem_detail_view where dbo.HD_WorkItem_detail_view.workitem_number='WORKITEM(workitem_number)'AND dbo.HD_WorkItem_detail_view.workitem_is_last = 1]]"
 
When: Every time incident is saved
 
And:
When ALL of these are TRUE
   "Status" changes and is equal to "Resolved"
<end>
 
Status: Active
 
Is terminal: No
 
Incident Rule #2:
 
Name: Set close time for waiting status
 
Comment: 2 Business Days
 
Set these properties:
Set "External ID" to "AEXQUERY[[Select dbo.[add_business_Minutes](dbo.HD_WorkItem_detail_view.workitem_modified_on, 1080,'9d785fd1-7789-4190-b5e5-0d478f5f15d9','885c5c5c-92b1-4049-8971-5f9c3935c8d9') from dbo.HD_WorkItem_detail_view where dbo.HD_WorkItem_detail_view.workitem_number='WORKITEM(workitem_number)' AND dbo.HD_WorkItem_detail_view.workitem_is_last = 1]]"
 
When: Every time incident is saved
 
And:
When ALL of these are TRUE
   "Status" is equal to "Waiting"
<end>
 
Status: Active
 
Is terminal: No

Statistics
0 Favorited
0 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

Oscar Castaneda
May 13, 2010 12:03 PM 

SELECT  
workitem_number AS 'Ticket_Number'  
FROM  
HD_workitem_current_view  
WHERE  
workitem_status_lookup_id = '400'  
AND  
DateDiff(hh,workitem_modified_on,GetDate()) > 24
SELECT  
workitem_number AS 'Ticket_Number'  
FROM  
HD_workitem_current_view  
WHERE  
workitem_status_lookup_id = '100'  
AND  
DateDiff(hh,workitem_modified_on,GetDate()) > 48

Related Entries and Links

No Related Resource entered.