Jasper Reports - Add one day to a Date Parameter

Question

I'm creating a Jasper report that includes the following parameters:

• DATESTART (Date)
• DATEEND (Date)

These parameters indicate a date range for a field called DATECREATED (Timestamp) which includes times. I would like the date range to be INCLUSIVE, that is, if I filter for "Jan 1, 2009" to "Jan 31, 2009", any DATECREATED value on Jan 31, 2009 (such as "Jan 31, 2009 15:00") will be included in the report.

When I used Crystal Reports in the past, I used the DATEADD function to create a filter expression like the following:

{DATECREATED} >= {DATESTART} and {DATECREATED} < DATEADD("d", 1, {DATEEND})

(I realize that this isn't syntactically correct, but you get the idea.)

Is there any way to do something similar in Jasper Reports?

Answer 1

If you understand French, there the same question is asked in this thread (the only difference is that it is about adding a month)

The proposed solutions are following:

SQL

Do it with SQL statement directly in the query (if your data source is a SQL datasource of course). With MySQL you can do something like

DATE_ADD($P{DATEEND},INTERVAL 1 DAY);

more information: Date and Time Functions (MySQL doc)

JAVA

The other solution is to use the Java possibly of the Date object:

I proposed something like :

$P{DATEEND}.setDay($P{DATEEND}.getDay()+1)

But I did not try it (and it is probably wrong).

Maybe you need to defined a new Date Variable DATEEND_1 with a value expression like :

new Date($P{DATEEND}.getTime() + 24*60*60*1000)

or

new java.util.Date($P{DATEEND}.getTime() + 24*60*60*1000)

And use this new variable in your query V{DATEEND_1}.

(again I am not sure of it)

Answer 2

You can create a class to help you, which has a static method which processes your parameters (or modify class JasperUtils by adding a static method). This way you can have amethod returning a boolean which will act exactly as the filter you need.

package com.package_name.utils;

public class JasperUtils2 {
  public static Boolean filterDate(Date dateStart, Date dateEnd, Date dateCreated) {
    if (dateCreated.compareTo(dateStart) < 0) return false; // dateCreated is greater or
                                                            // equal to dateStart
    if (dateCreated.compareTo(dateEnd) > 0) return false; // dateCreated is smaller or
                                                          // equal to dateEnd   
    // you can combine the two conditions into one. I wrote it like this to be more obvious
  }
}

To use this method, you need to import the class created (Edit -> Report import directives -> new import ---- this may differ for different versions of iReport). Then, you can use the method in static way:

JasperUtils2.filterDate(..., ..., ...)

Answer 3

Plz try this It will work

new java.util.Date($P{DATEEND}.getTime() + 24*60*60*1000)

Answer 4

Another option is to use the Groovy SDK that comes bundled with the latest versions.

new Date().plus(1) //Today plus one day = tomorrow.

Or to be more complete - add your two parameters DATESTART and DATEEND and set the default expression to be the above code. Then in your query add the following to the where clause:

DATECREATED >= $P{DATESTART} and DATECREATED < $P{DATEEND}

or depending on your SQL variant.

DATECREATED BETWEEN $P{DATESTART} AND $P{DATEEND}