Date Range Parameters in Jasper Reports with SQL Server

Ok, to some this might be obvious but I just spent about 4 hours trying to figure out how to use parameters to select a date range in a Jasper Report report based on a JDBC / SQL query to a Microsoft SQL Server.

First you need to define your parameter as a java.sql.Timestamp like this:

<parameter name="FROMDATE" class="java.sql.Timestamp">
		<defaultValueExpression><![CDATA[new java.sql.Timestamp(System.currentTimeMillis()-2628000000)]]></defaultValueExpression>
	</parameter>
	<parameter name="TODATE" class="java.sql.Timestamp">
		<defaultValueExpression><![CDATA[new java.sql.Timestamp(System.currentTimeMillis())]]></defaultValueExpression>
	</parameter>

notice how the default value is initialized to the current date, and the previous month!

Then you can pass the parameter like this:

ORDER_DATE between cast('$P!{FROMDATE}' as date) and cast('$P!{TODATE}' as date)

If you made it this far, you may as well follow me on LinkedIn: Follow Brian Porter