How to compare date in database with current date

[jonathanln]

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 like

Integer.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

[jonathanln]

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.

[mdahlman]

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 beforetoday
FROM
   ...

 

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

[jonathanln]

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