When trying to use the DATETOLONG function in a 6 month KPI report (Query pasted below) I receive the following error message:

 java.lang.Exception: Syntax error : function LONGTODATE not closed properly! at com.adventnet.servicedesk.reports.utils.ReportUtil.parseDateCriteriaColumns(ReportUtil.java:2064) at ...

Which is odd as the query doesn't use the LONGTODATE function.

Query:

SELECT COUNT(wo.WORKORDERID) 'Total',(SELECT COUNT(wo.WORKORDERID) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN WorkOrder_Threaded wot ON wo.WORKORDERID=wot.WORKORDERID WHERE wot.WORKORDERID=wot.THD_WOID and wos.STATUSID=1 AND (wo.CREATEDTIME >= DATETOLONG('2009-05-01 00:00:00')) AND (wo.CREATEDTIME <= DATETOLONG('2009-10-31 00:00:00'))) 'Open',(SELECT COUNT(wo.WORKORDERID) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN WorkOrder_Threaded wot ON wo.WORKORDERID=wot.WORKORDERID WHERE wot.WORKORDERID=wot.THD_WOID AND wos.STATUSID=3 AND (wo.CREATEDTIME >= DATETOLONG('2009-05-01 00:00:00')) AND (wo.CREATEDTIME <= DATETOLONG('2009-10-31 00:00:00'))) 'Closed',(SELECT COUNT(wo.WORKORDERID) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN WorkOrder_Threaded wot ON wo.WORKORDERID=wot.WORKORDERID WHERE wot.WORKORDERID=wot.THD_WOID AND wos.STATUSID=3 and wo.COMPLETEDTIME > wo.DUEBYTIME AND (wo.CREATEDTIME >= DATETOLONG('2009-05-01 00:00:00')) AND (wo.CREATEDTIME <= DATETOLONG('2009-10-31 00:00:00'))) 'Not met SLA',(SELECT COUNT(wo.WORKORDERID) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN WorkOrder_Threaded wot ON wo.WORKORDERID=wot.WORKORDERID WHERE wot.WORKORDERID=wot.THD_WOID AND wos.STATUSID=3 AND wo.COMPLETEDTIME < wo.DUEBYTIME AND (wo.CREATEDTIME >= DATETOLONG('2009-05-01 00:00:00')) AND (wo.CREATEDTIME <= DATETOLONG('2009-10-31 00:00:00'))) 'Closed within SLA' FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN WorkOrder_Threaded wot ON wo.WORKORDERID=wot.WORKORDERID WHERE wot.WORKORDERID=wot.THD_WOID AND (wo.CREATEDTIME >= DATETOLONG('2009-05-01 00:00:00')) AND (wo.CREATEDTIME <= DATETOLONG('2009-10-31 00:00:00'))

I've read and re-read the query ensuring that all quotes are closed and all brackets are matching pairs.

(Edit:) We are using this version:

Thanks, Richard