Date Parameter Issue

Date Parameter Issue

Posted by: jschmitz on 19 March 2025, 4:40 pm EST

• 

  jschmitz

  • Post Options:
  • Link
    Copy

  Posted 19 March 2025, 4:40 pm EST

  I am trying to create a report that uses a date parameter.
  
  Here is the query:
  
  SELECT ISSUENO “TICKET COUNT”,
  
  TO_CHAR(ISSUEDATE, ‘MM/DD/YYYY’) “ISSUE DATE”,
  
  CASE WHEN VOIDREASON LIKE ‘%PAYPLAN%’ THEN ‘PAYMENT PLAN’
  
  WHEN VOIDREASON LIKE ‘Remove Invalid%’ AND EXTRACT(YEAR FROM RESUMEDATE) = ‘2099’ THEN ‘PERMANENT SUSPEND’
  
  ELSE VOIDREASON END “SUSPEND CODE”,
  
  CASE WHEN VIOCODE LIKE ‘000AUC%’ AND ISSUENO LIKE ‘99%’ THEN ‘AUCTION TICKET’
  
  ELSE ‘PARKING’ END “TYPE”,
  
  AMOUNTDUE “AMOUNT DUE”
  
  FROM PHILLY.PARKCITE
  
  WHERE ISSUEDATE < to_date(:WriteOffDate, ‘MM/DD/YYYY’) - 1825
  
  AND (AMOUNTDUE > 0 AND ISSUEDATE <= to_date(:WriteOffDate, ‘MM/DD/YYYY’) - 5475)
  
  OR (AMOUNTDUE < 10 AND ISSUEDATE <= to_date(:WriteOffDate, ‘MM/DD/YYYY’) - 5475)
  
  AND EXTRACT(YEAR FROM ISSUEDATE) <> ‘1899’
  
  AND (VOIDREASON NOT LIKE ‘%PAYPLAN%’ AND VOIDREASON NOT LIKE ‘%IPP%’ AND VOIDREASON NOT LIKE ‘426%’ OR VOIDREASON IS NULL)
  
  AND PLANUNIQUEKEY IS NULL
  
  AND (SECTOR3 IS NULL OR SECTOR3 = ‘PPA’)
  
  AND VIOCODE NOT LIKE ‘1379%’
  
  AND AMOUNTDUE > 0
  
  ORDER BY 1 DESC

  and here is the error:
  
  ORA-01843: not a valid month’
  
  It looks to me like the date parameters do not allow for subtraction of days.
  
  Is there any way around this issue.
  
  My version is Active Reports 14

• 

  akshay.rajput

  

  • Post Options:
  • Link
    Copy

  Posted 25 March 2025, 6:34 am EST

  Hi Jessica,

  I tested out the attached report with SQL Data and it seems to be working fine at my end.
  
  I used the following Query to get data from selected date and last 6 months:

  SELECT * FROM Orders 
  WHERE OrderDate BETWEEN DATEADD(MONTH, -6, @OrderDate) AND @OrderDate
  ORDER BY OrderDate;
  

  For Oracle the query would be something like:

  SELECT * FROM Orders 
  WHERE OrderDate BETWEEN ADD_MONTHS(@OrderDate, -6) AND @OrderDate
  ORDER BY OrderDate;
  

  Please refer to the attached sample report. IF the issue persist then please share report with dummy data so we can test this issue at our end and get back to you accordingly.