Oracle WHERE-clause "Equal" with date

I read email:

I had a table sales_dates on that there are 200 records,
in which ship_date is a column having date value .

SELECT SHIP_DATE FROM SALES_DATES WHERE SHIP_DATE = '07-DEC-07';

i get no rows selected .


This is a good question;
Actually A DATE can not equal to "07-DEC-07", because it has time component as well.

So:

SQL> desc SALES_DATES
Name Null? Type
----------------------------------------- -------- ----------------------------
SHIP_DATE DATE


SQL> select SHIP_DATE FROM SALES_DATES;

SHIP_DATE
---------
19-MAR-08
19-MAR-08
19-MAR-08
19-MAR-08

SQL> select sysdate from dual;
SYSDATE
---------
19-MAR-08

SQL> select SHIP_DATE FROM SALES_DATES where SHIP_DATE = '19-MAR-08';

no rows selected


Don't see any data, So modify SQL statement to:

SELECT SHIP_DATE FROM SALES_DATES WHERE to_char(SHIP_DATE,'DD-MON-YY') = '19-MAR-08'

or

SELECT SHIP_DATE FROM SALES_DATES WHERE trunc(SHIP_DATE) = '19-MAR-08'


SQL> SELECT SHIP_DATE FROM SALES_DATES WHERE to_char(SHIP_DATE,'DD-MON-YY') = '19-MAR-08'
2 /

SHIP_DATE
---------
19-MAR-08
19-MAR-08
19-MAR-08
19-MAR-08


SQL> SELECT SHIP_DATE FROM SALES_DATES WHERE trunc(SHIP_DATE) = '19-MAR-08';

SHIP_DATE
---------
19-MAR-08
19-MAR-08
19-MAR-08
19-MAR-08


Thanks bala : he informs me know about index (because I didn't use good statement):

SQL> select ship_date from sales_dates where ship_date between to_date('19-MAR-08', 'dd-MON-yy') and to_date('19-MAR-08 23:59:59', 'DD-MON-YY hh24:mi:ss')

SHIP_DATE
---------
19-MAR-08
19-MAR-08
19-MAR-08
19-MAR-08

This is the better statment than other, it uses index:

SQL> set autot trace explain

SQL> SELECT SHIP_DATE FROM SALES_DATES WHERE to_char(SHIP_DATE,'DD-MON-YY') = '19-MAR-08' ;


Execution Plan
----------------------------------------------------------
Plan hash value: 3826751543
---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 1 8 3 (0) 00:00:01
* 1 TABLE ACCESS FULL SALES_DATES 1 8 3 (0) 00:00:01
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_CHAR(INTERNAL_FUNCTION("SHIP_DATE"),'DD-MON-YY')='19-MAR-08')

SQL> SELECT SHIP_DATE FROM SALES_DATES WHERE trunc(SHIP_DATE) = '19-MAR-08';

Execution Plan
----------------------------------------------------------
Plan hash value: 3826751543
---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 1 8 3 (0) 00:00:01
* 1 TABLE ACCESS FULL SALES_DATES 1 8 3 (0) 00:00:01
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TRUNC(INTERNAL_FUNCTION("SHIP_DATE"))='19-MAR-08')


SQL> select ship_date from sales_dates where ship_date between to_date('19-MAR-08', 'dd-MON-yy') and to_date('19-MAR-08 23:59:59', 'DD-MON-YY hh24:mi:ss') ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3492603898
----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 4 32 1 (0) 00:00:01
* 1 FILTER
* 2 INDEX RANGE SCAN INDX01 4 32 1 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('19-MAR-08','dd-MON-yy')<=TO_DATE('19-MAR-08
23:59:59','DD-MON-YY hh24:mi:ss'))

2 - access("SHIP_DATE">=TO_DATE('19-MAR-08','dd-MON-yy') AND
"SHIP_DATE"<=TO_DATE('19-MAR-08 23:59:59','DD-MON-YY hh24:mi:ss'))

Enjoy!