Video Screencast Help
Search Video Help Close Back
to help

Auto Close Notification Policy will not pull GetDate() properly

Created: 05 May 2010 | Updated: 10 May 2010 | 1 comment
Oscar2564's picture
Oscar2564 Partner
0 0 Votes
Login to vote

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
Blog Entry Filed Under:
, , , , , , , , , ,
Group Ownership:

Comments 1 Comment • Jump to latest comment

Oscar2564's picture
Oscar2564 Partner
Auto Close Notification Policy will not pull GetDate() properly - Comment:13 May 2010 : Link
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

Oscar H. Castañeda
Technical Solutions Consultant
XCEND Group Inc. -- Symantec Platinum Partner

+1
Login to vote