Date Format in SQL query ORA-01858 error

[cyyung1023]

The following line will throw ORA-01858 error in my sql query statement, but I am not sure what is the problem.

LAST_DAY(ADD_MONTHS(to_date('01/' || $P{Report_Month} || '/' || $P{Report_Year}, 'dd/mm/yyyy'),'-1')) 

Also, will the following line also give the same error?

table1.create_date >= TRUNC(ADD_MONTHS(to_date('01/' || $P{Report_Month} || '/' || $P{Report_Year}, 'dd/mm/yyyy'),'-1'), 'MM') and
table1.create_date <= LAST_DAY(ADD_MONTHS(to_date('01/' || $P{Report_Month} || '/' || $P{Report_Year}, 'dd/mm/yyyy'),'-1'))

[jgust]

I tried to recreate the issue but it ended up working out for me.  I thought the problem might have been that you needed to use the $P!{param} instead of $P{param}.

From <https://community.jaspersoft.com/questions/529203/pparam-vs-pparam>

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.14.0.final using JasperReports Library version 6.14.0-2ab0d8625be255bf609c78e1181801213e51db8f  --><!-- 2020-10-01T19:41:14 --><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Blank_A4" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="e1782c2e-c5e4-4423-ad39-79bee2b73b95">    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="MyDB"/>    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <parameter name="Report_Month" class="java.lang.String"/>    <parameter name="Report_Year" class="java.lang.String"/>    <queryString>        <![CDATA[select  LAST_DAY(ADD_MONTHS(to_date('01/' ||  $P{Report_Month} || '/' ||  $P{Report_Year}, 'dd/mm/yyyy'),'-1')) as EOMfrom dual]]>    </queryString>    <field name="EOM" class="java.sql.Timestamp">        <property name="com.jaspersoft.studio.field.name" value="EOM"/>        <property name="com.jaspersoft.studio.field.label" value="EOM"/>    </field>    <columnHeader>        <band height="14" splitType="Stretch">            <staticText>                <reportElement x="43" y="0" width="100" height="14" uuid="fb479672-9b00-4bfe-8790-32d6172034a6">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="18f8a949-90d7-41f0-8cf5-13f5a4353ae2"/>                </reportElement>                <text><![CDATA[EOM]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="17" splitType="Stretch">            <textField textAdjust="StretchHeight" pattern="MM/dd/yyyy">                <reportElement x="43" y="0" width="100" height="17" uuid="709b53cf-a4da-42a7-b5fc-75d117869435">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="18f8a949-90d7-41f0-8cf5-13f5a4353ae2"/>                </reportElement>                <textFieldExpression><![CDATA[$F{EOM}]]></textFieldExpression>            </textField>        </band>    </detail></jasperReport>[/code]