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!
Posts
