Date parameters issue with 1.2

[RyanWyse]

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

[RyanWyse]

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

[lucianc]

How are the start_date and end_date declared (in JRXML)?

 

Regards,

Lucian

[RyanWyse]

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>

[lucianc]

Do you use the same JDBC driver in iReport and JasperServer?

[RyanWyse]

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

[anandharaj]

Maybe you can try this way

 

Code:

AND (DATE(date_entered) BETWEEN $P{start_date} AND $P{end_date})

[bhaugland]

I am having the same problem only with an oracle database. One date shows up as EST and the next date shows up as EDT. Is there a problem with the daylight savings time switch in Jasper.

[bhaugland]

I am having the same problem only with an oracle database. One date shows up as EST and the next date shows up as EDT. Is there a problem with the daylight savings time switch in Jasper.

[bhaugland]

I am having the same problem only with an oracle database. One date shows up as EST and the next date shows up as EDT. Is there a problem with the daylight savings time switch in Jasper.