jonathanln Posted June 30, 2009 Share Posted June 30, 2009 Hi, I read a lot on how to compare dates together but I still can't figure out how to solve my problem.I have a column in my table filled with dates and I have to count how many "dates in column" <= current date.The format in the table is 'dd/mm/yyyy' and the type is DATE==========================My idea was to create a variable with "Calculation" set to "sum" and variable expression set to something likeInteger.valueOf( $F{table_date} <= sysdate ? 0 : 1)but I haven't been able to use "sysdate" outside of the main report query and "<" doesn't seem to be accepted.==========================Thank you in advance for your help,John Link to comment Share on other sites More sharing options...
jonathanln Posted June 30, 2009 Author Share Posted June 30, 2009 I found the solution : Integer.valueOf( $F{aaaa}.before(new java.util.Date()) ? false : true ) aaaa being the column filled with dates in my table. Link to comment Share on other sites More sharing options...
mdahlman Posted June 30, 2009 Share Posted June 30, 2009 John, Thanks for posting the solution for others! Another way to address this is to do the date calculation in the query. Use something like this:SELECT mydatefield as mydatefield, CASE WHEN mydatefield < sysdate THEN 1 ELSE 0 END as beforetodayFROM ... Then in iReport your variable is an easy-to-understand sum on the field "beforetoday". And as a nit-picking detail, this sentence is not really correct: "The format in the table is 'dd/mm/yyyy' and the type is DATE". If it's a date column in the database then it does not have a format. There is a default display format based on server and/or client settings. But the underlying data is just a date with no formatting information attached. Again, thanks for taking the time to post a solution. It helps to make the forums more useful to everyone. Regards,Matt Link to comment Share on other sites More sharing options...
jonathanln Posted July 2, 2009 Author Share Posted July 2, 2009 Thanks a lot to you Matt for these precisions and the alternative way of solving this problem. You helped me, and I hope others too, to understand iReport a little bit better. John 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