Jump to content
Changes to the Jaspersoft community edition download ×

Variables and Dates

Recommended Posts

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 name="SQLEndDate" class="java.lang.String" resetType="Report" calculation="Nothing">

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



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




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.


Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...