RyanWyse Posted March 21, 2007 Share Posted March 21, 2007 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 More sharing options...
RyanWyse Posted March 21, 2007 Author Share Posted March 21, 2007 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 More sharing options...
lucianc Posted March 21, 2007 Share Posted March 21, 2007 How are the start_date and end_date declared (in JRXML)? Regards,Lucian Link to comment Share on other sites More sharing options...
RyanWyse Posted March 21, 2007 Author Share Posted March 21, 2007 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 More sharing options...
lucianc Posted March 21, 2007 Share Posted March 21, 2007 Do you use the same JDBC driver in iReport and JasperServer? Link to comment Share on other sites More sharing options...
RyanWyse Posted March 22, 2007 Author Share Posted March 22, 2007 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 More sharing options...
anandharaj Posted March 24, 2007 Share Posted March 24, 2007 Maybe you can try this way Code:AND (DATE(date_entered) BETWEEN $P{start_date} AND $P{end_date}) Link to comment Share on other sites More sharing options...
bhaugland Posted March 29, 2007 Share Posted March 29, 2007 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. Link to comment Share on other sites More sharing options...
bhaugland Posted March 29, 2007 Share Posted March 29, 2007 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. Link to comment Share on other sites More sharing options...
bhaugland Posted March 29, 2007 Share Posted March 29, 2007 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now