Ireport - Months between dates

[jongr]

I want to create an expression to calculate the number of months between two dates (preferably in groovy)

and then multiply the result by a field value.

Can anyone help - thanks

[jmurray]

Date calculations are a tricky thing. See http://www.xmission.com/~goodhill/dates/deltaDates.html and http://www.exit109.com/%7Eghealton/y2k/yrexamples.html for some examples.

 

 

It's often easier to do this sort of thing in SQL where rudimentary date calculation methods are already available.

 

 

In Oracle you can use the built in MONTHS_BETWEEN(Date1, Date2) function.

 

 

MySQL has PERIOD_DIFF(P1,P2) but P1 and P2 are not Date values. They are date formatted integers of the form YYMM or YYYYMM. eg. SELECT PERIOD_DIFF(9802,199703) from dual;

[jongr]

Ok, I will try that, but before I do I will need to convert my Parameter date into YYYYMM so that I can use it in MySQL

(The parameter is using the type java.util.Date)

Can you suggest the best way ?

Thanks

[nittu02]

cant you set that using patterns in ireport??

[jmurray]

In MySQL you would convert your date to 'YYYMMM' format using DATE_FORMAT(datefield,'%Y%c'). Turns out that PERIOD_DIFF function handles the two date serial numbers as numbers or strings. Have fun.

 

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

[jongr]

Thanks to jmurry, it works fine

note: I had to change

DATE_FORMAT(datefield,'%Y%c')

to DATE_FORMAT(datefield,'%Y%m')

so that I got two digit months

[jmurray]

Ah yes, very clumsy of me to miss that! Apologies.

[jazpiroz]

i need to calculate sum

date1 + 285

 

i cant do it,

 

please give a tutorial or manual

 

thanks a lot

 

juan from uruguay