Jasper - Oracle - How to filter by date

Question

I am trying to filter a query by date.

I have this line in my query:

AND (the_date like CONCAT (TO_DATE($P{THE_DATE}, 'YYYY-MM-DD'),'%'))

However, I just cannot seem to compare the parameter date to the database date effectively.

Database date is of type DATE. Parameter is of type String.

I've also tried:

AND (TO_CHAR(the_date) like CONCAT ($P{THE_DATE,'%')

Are my data types wrong? I've tried others but to no avail. Is my query wrong?

I'm using iReport... I looked for some kind of debugging option to see what is actually being executed but didn't find any.

Answer 1

When you want to compare DATEs, you need to convert the literal into DATE using TO_DATE.

No need to use LIKE operator. You could either useTRUNC on the DATE column, however, that would suppress any regular index usage. It would be better to use a DATE RANGE condition.

Remember, DATE has both date and time elements.

For example,

WHERE
   the_date >= TO_DATE('14-MAY-2015','DD-MON-YYYY')
AND 
   the_date < TO_DATE('14-MAY-2015','DD-MON-YYYY') +1;

Instead of literals in above example, you could use your INPUT parameter or the local variable which you have defined as string.

WHERE
   the_date >= TO_DATE(in_date,'DD-MON-YYYY')
AND 
   the_date < TO_DATE(in_date,'DD-MON-YYYY') +1;