need to offset a date by intervals of 1 year

[mperrone]

Hello -

I am trying to design a report with multiple run dates (i.e. a series of subreports that use date parameters that offset from the prompted date parameters by an integer value of 1 - 4 years).  I first tried to use the DATEADD function in my SQL statement but iReport tells me the function doesn't exist.  So when that didn't work I tried to set up each offset date as a variable.  I figured out that using groovy I could simply create a variable with the expression:

$P{rundate} - 365

but that won't work thanks to leap years.  Any idea how I can offset a date using x number of years?

Thanks,

- Mike Perrone

 

[mdahlman]

Mike,

My first thought would be to do this calculation in the SQL. You don't mention your database technology. MySQL uses "DATE_ADD", other databases use different terminology. If you're getting a message that your function doesn't exist, but it's not really iReport giving you this information. iReport is just the bearer of bad news in this case. It's your database telling you that the function doesn't exist. If you tweak your syntax, you can certainly do this in the SQL query.

But if for whatever reason you prefer to do the calculation in the report, then I recommend using the functions provided in the Java Commons Lang project. (Valid reasons include: "I want my report to be cross-database compatible", "I don't like SQL", and "I just want to".) Java's built-in data functions are not very helpful for this type of thing. I wrote an article about date functions and JasperReports and Java Commons Lang a while back because it's such a common need. It's here: Right Outer Join.

Regards,
Matt

[ckampshoff]

Hi Mike,

I use the follwing syntax in the Default value:  new Date(new GregorianCalendar($P{rundate} .getYear() + 1900 - 1, $P{rundate} .getMonth(), $P{rundate} .getDay()).getTime().getTime())

You need to add 1900 to the getYear() function to get the right year of your Parameter.

HTH

Christina

 

[mperrone]

Hey Matt -

Oops - yeah, we do use MySQL.  And I had tried using DATE_ADD with the underscore; that syntax didn't work either.  But it is quite helpful to know that when the SQL syntax doesn't work that it doesn't have anything to do with iReport... I had always wondered if our database and iReport were connected in such a way that iReport could somehow effect the way a SQL statement executes.

(Our database adminstrator is a consultant for whom we're trying to keep hours down, and I'm an accountant who's trying to figure out how to generate some reports somewhat inexpensively... thus the gaping holes in my knowledge foundation.)

Ideally I would prefer to handle all of this date manipulation through SQL than in the report itself, but I'll take whatever works.

I just imported the Java Commons Lang package that you referenced and experimented with the expression for my date object, but got a "DateUtils cannot be resolved" error message.  At some point I will look through the jrxml file for your sample report to see if I can figure out what the problem is... for now the java expression that Christina posted below does work for me, so I'm going to run with that in the interest of getting this done ASAP.

Thank you for your help!

- Mike

[mperrone]

Thank you Christina!!  That does work!

- Mike

 

[mdahlman]

Mike,

If that solution works... then I guess you're all set. But that's an awful lot of code to subtract dates. It makes me cringe.

I suspect that you ran into this fun bit of MySQL syntax. The syntax you want is this:
select date_add('2009-01-01', INTERVAL 1 YEAR);
2010-01-01

But you were likely ever-so-close with something like this:
select date_add ('2009-01-01', INTERVAL 1 YEAR);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual...

Yes, that's right... MySQL gags on a SPACE between DATE_ADD and the initial parenthesis. It's enough to make puppies cry.

Regards,
Matt

[mperrone]

Yeah it was a lot of code.  Duh... I think I mixed up Transact-SQL with MySQL when I tried writing out the DATE_ADD statement... I tried it with and without the space, but I had the arguments listed out as (YEAR, -1, '2009-01-01').  Don't know how I missed that.

Thanks Matt, much appreciated.

- Mike