Normally when we need to compare DATE type where only DATE (i.e DD/MM/YYYY) component holds the significant, we need to convert DATE into VARCHAR2 or use TRUNCATE as follows so that only date component is compare(not time):

Assume I have index on balance_date.

SELECT balance_amount
FROM balances
WHERE
TO_CHAR(balance_date,’YYYYMMDD’)
= TO_CHAR(sysdate,’YYYYMMDD’);

After running the explain plan for above statement it shows me:

PLAN_TABLE_OUTPUT
—————————————
Plan hash value: 4006579748

—————————————
| Id | Operation | Name |
—————————————
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| BALANCES |
—————————————

OR

SELECT balance_amount
FROM balances
WHERE TRUNC(balance_date) = TRUNC(sysdate);

After running the explain plan for above statement it shows me:

PLAN_TABLE_OUTPUT
—————————————
Plan hash value: 4006579748

—————————————
| Id | Operation | Name |
—————————————
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| BALANCES |
—————————————

We notice that even though we have index on balance_date, its use is suppress due to use of function on balance_date.

We can perform same comparison with better efficiency by rewriting above queries as follows:

SELECT balance_amount
FROM balances
WHERE
balance_date BETWEEN
TRUNC(sysdate)
AND
TO_DATE( TO_CHAR(sysdate,’YYYYMMDD’)||’23:59:59′,’YYYYMMDDHH24:MI:SS’);

or based on zfriese comments:

SELECT balance_amount
FROM balances
WHERE
balance_date >= TRUNC(sysdate)
AND balance_date < TRUNC(sysdate) + 1

PLAN_TABLE_OUTPUT
—————————————————-
Plan hash value: 3073005708

—————————————————-
| Id | Operation | Name
—————————————————-
| 0 | SELECT STATEMENT |
|* 1 | FILTER |
| 2 | TABLE ACCESS BY INDEX ROWID| BALANCES
|* 3 | INDEX RANGE SCAN | BALANCES_IDX
—————————————————-