Jump to content
Changes to the Jaspersoft community edition download ×

Date parameters issue with 1.2


RyanWyse

Recommended Posts

I am having a problem with date parameters in my SQL queries after upgrading to jasperserver 1.2. I run mysql 5.026. My report query has the following section in it:

 

Code:
AND date_entered < $P{end_date} 
AND date_entered > $P{start_date}

 

which when I run the report in iReport produces the following query (I have query logging turned on in mysql):

Code:
[code]
AND date_entered < '2007-03-20'
AND date_entered > '2007-02-18'

 

which works fine and the report runs fine.

 

When I run the report in jasperserver it produces the following query segment:

 

Code:
[code]AND date_entered < 'Tue Mar 20 14:40:50 CDT 2007'
AND date_entered > 'Sun Feb 18 14:40:50 CST 2007'

 

which runs, but does not work properly.

 

These results are the same whether I let it use the parameter default of new Date(), or if I specify the date at run time.

 

I think I can change my SQL query to work around it. But if there is way to tell jasperserver to format the date before sending it in the SQL query that would be better.

 

Thanks,

 

Ryan

Link to comment
Share on other sites

  • Replies 9
  • Created
  • Last Reply

Top Posters In This Topic

I have the following SQL hack in place for MySQL to get around this:

 

instead of

 

Code:
$P{start_date}

 

I use:

 

Code:
[code]IF (str_to_date(concat(substring($P{start_date}, 1, 19), substring($P{start_date},24,5)), '%a %b %e %H:%i:%s %Y') IS NULL, $P{start_date}, str_to_date(concat(substring($P{start_date}, 1, 19), substring($P{start_date},24,5)), '%a %b %e %H:%i:%s %Y'))

 

Which basically reformats what it receives from jasperserver for dates and makes it something mysql likes. If the reformat doesn't work for some reason it just passes the date as is, this lets me use the same query in jasperserver and iReport. (It also means my reports won't break when jasperserver gets fixed) :)

 

Ryan

Link to comment
Share on other sites

Code:
        <parameter name="start_date" isForPrompting="true" class="java.util.Date">
<parameterDescription><![CDATA[start Date]]></parameterDescription>
<defaultValueExpression ><![CDATA[new java.util.Date()]]></defaultValueExpression>
</parameter>
<parameter name="end_date" isForPrompting="true" class="java.util.Date">
<parameterDescription><![CDATA[End Date]]></parameterDescription>
<defaultValueExpression ><![CDATA[new java.util.Date()]]></defaultValueExpression>
</parameter>
Link to comment
Share on other sites

I'm using what comes with iReport and jasperserver.

 

Which for iReport 1.3.1 is:

 

mysql-connector-java-3.1.11-bin.jar

 

And for jasperserver 1.2 is:

 

mysql-connector-java-3.1.12-bin.jar

 

I tried replacing the jasperserver jar with the iReport jar and that didn't change anything.

 

Ryan

Link to comment
Share on other sites

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...