CODEWITHSUNDEEP
jasper-reports
Toshan
Toshan

Reputation: 23

How to use date range type parameter as normal java.util.date in jasper

I have created two input controllers BeginDate and EndDate as date range type. I need to convert that dates which are input as date range to util.date.These are data types I have created.

<parameter name="BeginDate" class="net.sf.jasperreports.types.date.DateRange"/>
<parameter name="EndDate" class="net.sf.jasperreports.types.date.DateRange"/>

  <![CDATA[SELECT * 
FROM table 
WHERE $X{BETWEEN,date,BeginDate,EndDate}
    AND total > 0;]]>

I need to use that dates in below.

   <![CDATA[SELECT * 
    FROM table 
    WHERE 
date >= $P{BeginDate} AND
date < $P{EndDate}
        AND total > 0;]]>

Upvotes: 0

Views: 3458

Answers (2)

Ryan
Ryan

Reputation: 1

You could have used your original query listed above by simply adding the [ character before BETWEEN. See updated query below:

<![CDATA[SELECT * FROM table WHERE $X{[BETWEEN, date, BeginDate, EndDate} AND total > 0;]]>

The following blog explains the various $X clause functions and includes examples of each: https://milangadajaspersoft.blogspot.com/2018/11/x-parameter-in-jasper.html

Upvotes: 0

Narcis
Narcis

Reputation: 2511

The DateRange type provides 2 methods: getStart() and getEnd() to get the beginning and end of the range. These methods return java.util.Date objects which you could use further.

But because the DateRange type is not allowed in the query expression you need to create new parameters of type java.util.Date to use as you want:

<parameter name="BeginDate_start" class="java.util.Date">
    <defaultValueExpression><![CDATA[$P{BeginDate}.getStart()]]></defaultValueExpression>
</parameter>
<parameter name="EndDate_end" class="java.util.Date">
    <defaultValueExpression><![CDATA[$P{EndDate}.getEnd()]]></defaultValueExpression>
</parameter>

Then your query may look like so:

<queryString>
    <![CDATA[SELECT * FROM table 
WHERE date >= $P{BeginDate_start} AND date < $P{EndDate_end} AND total > 0]]>
</queryString>

Upvotes: 2

Related Questions