How to pass datetime parameter to postgresql sql?

I am using Jaspersoft studio to create a report. The data adapter is a postgresql query (to a postgres server). The query:

select * from dbo.FACTSALES where FactSales.saledatekey BETWEEN to_char($P!{StartDate}, 'YYYYMMDD')::INTEGER  AND to_char($P!{EndDate}, 'YYYYMMDD')::INTEGER

FactSales.saledatekey is Integer.

My plan is to put two data time parameters (StartDate, EndDate) in the report so I can choose start and end dates to run the report. So the query is as above and I also created two parameters in Outline with the same names and of type java.sql.Timestamp (no default expression). But When i run the report I get Error executing SQL Statement.

EDIT

so I changed $P! to $P

select * from dbo.FACTSALES where FactSales.saledatekey BETWEEN to_char($P{StartDate}, 'YYYYMMDD')::INTEGER  AND to_char($P{EndDate}, 'YYYYMMDD')::INTEGER

But I still get the same error.

jrxml (I changed from select * to just two columns):

<?xml version="1.0" encoding="UTF-8"?>
<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="test4" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="fd68b751-49c2-4153-b2a8-48a95af021c9">
        <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="datamart"/>
        <property name="ireport.jasperserver.url" value="http://10.20.169.43:8080/jasperserver/"/>
        <property name="ireport.jasperserver.user" value="Nian"/>
        <property name="ireport.jasperserver.report.resource" value="/reports/NianTest/test4_files/main_jrxml"/>
        <property name="ireport.jasperserver.reportUnit" value="/reports/NianTest/test4"/>
        <parameter name="StartDate" class="java.sql.Timestamp">
            <parameterDescription><![CDATA[]]></parameterDescription>
        </parameter>
        <parameter name="EndDate" class="java.sql.Timestamp"/>
        <queryString>
            <![CDATA[select  customerkey, productkey from dbo.FACTSALES where $X{[BETWEEN], FactSales.saledatekey , to_char($P{StartDate} , 'YYYYMMDD')::INTEGER, to_char($P{EndDate}, 'YYYYMMDD')::INTEGER}]]>
        </queryString>
        <field name="customerkey" class="java.lang.Integer"/>
        <field name="productkey" class="java.lang.Integer"/>
        <background>
            <band splitType="Stretch"/>
        </background>
        <title>
            <band height="79" splitType="Stretch"/>
        </title>
        <pageHeader>
            <band height="35" splitType="Stretch"/>
        </pageHeader>
        <columnHeader>
            <band height="61" splitType="Stretch">
                <staticText>
                    <reportElement x="50" y="0" width="100" height="30" uuid="1471918d-b246-4aea-ba5c-d65a7fa48284"/>
                    <text><![CDATA[customerkey]]></text>
                </staticText>
                <staticText>
                    <reportElement x="268" y="0" width="100" height="30" uuid="361e8408-7859-483f-8143-5834fff3594d"/>
                    <text><![CDATA[productkey]]></text>
                </staticText>
            </band>
        </columnHeader>
        <detail>
            <band height="125" splitType="Stretch">
                <textField>
                    <reportElement x="50" y="49" width="100" height="30" uuid="3423ea0c-aa51-4537-ab28-29ae255c313c"/>
                    <textFieldExpression><![CDATA[$F{customerkey}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="200" y="40" width="100" height="30" uuid="d8381278-1fc1-4f9f-bd85-e65a85b77630"/>
                    <textFieldExpression><![CDATA[$F{productkey}]]></textFieldExpression>
                </textField>
            </band>
        </detail>
        <columnFooter>
            <band height="45" splitType="Stretch"/>
        </columnFooter>
        <pageFooter>
            <band height="54" splitType="Stretch"/>
        </pageFooter>
        <summary>
            <band height="42" splitType="Stretch"/>
        </summary>
</jasperReport>

and two parameters:

StartDate class: java.sql.Timestamp
EndDate class: java.sql.Timestamp

The error:

ERROR: function to_char(unknown, unknown) is not unique Hint: Could not choose a best candidate function. You might need to add explicit type casts

Solution

The query using prepared statement will be:

select customerkey, productkey from dbo.FACTSALES where FactSales.saledatekey BETWEEN to_char($P{StartDate}::timestamp, 'YYYYMMDD')::int  AND to_char($P{EndDate}::timestamp, 'YYYYMMDD')::int

or change you parameter to Integer and use the build-in between function

select customerkey, productkey from dbo.FACTSALES where $X{[BETWEEN], FactSales.saledatekey ,StartDate, EndDate}

If you like to test in IDE always consider to set default values to parameters.

<parameter name="StartDate" class="java.sql.Timestamp" isForPrompting="false">
    <defaultValueExpression><![CDATA[new java.sql.Timestamp(1l)]]></defaultValueExpression>
</parameter>
<parameter name="EndDate" class="java.sql.Timestamp" isForPrompting="false">
    <defaultValueExpression><![CDATA[new java.sql.Timestamp(new java.util.Date().getTime())]]></defaultValueExpression>
</parameter>