Variables and Dates

[2002 JI Open Discussion]

By: Chris Green - c_g12

Variables and Dates

2003-04-08 12:57

Is it possible to include variables in the SQL Query?

 

The reason I ask is that I want my date parameters to be of class java.util.Date so that I can dynamically query a JasperReport object for its parameters and generate an appropriate UI that corresponds to the class(a calendar for Dates, checkboxes for booleans, etc).

 

I thought I would be able to use a Variable to format the Date parameter into a string that could be included in the Query:

<variable name="SQLStartDate" class="java.lang.String" resetType="Report" calculation="Nothing">

<variableExpression><![CDATA[(new SimpleDateFormat("yyyy-MM-dd")).format($P{StartDate})]]></variableExpression>

</variable>

<variable name="SQLEndDate" class="java.lang.String" resetType="Report" calculation="Nothing">

<variableExpression><![CDATA[(new SimpleDateFormat("yyyy-MM-dd")).format($P{EndDate})]]></variableExpression>

</variable>

 

Then I could include $V{SQLStartDate} and $V{SQLEndDate} in my SQL query in the JasperReport. However this doesn't seem to work so I thought I'd check to see if it's even possible. If not, is there any other way to include java.util.Date objects in the SQL query?

 

 

 

 

By: Teodor Danciu - teodord

RE: Variables and Dates

2003-04-08 20:55

 

Hi,

 

Only report parameters can be used in the query.

Report variables are evaluated after the query

is executed, so this is why we cannot use them.

 

You either supply a Date parameter to your report

and use it in the query directly, or you can format it into a String using the <defaultValueExpression> of a second String parameter, before using

it in the query.

Make sure you do not pass a value for this second

report parameter so that the <defaultValueExpression> gets triggered.

 

If the table column in the data base is of type date or datetime, I suggest you let the parameter be a

java.util.Date, as the JDBC driver would know how to handle it.

 

I hope this helps.

Teodor