My guess is that your test data contains two dates with the year 1902.
09:18:49 SQL> 09:18:49 SQL>create table d( d date ); Table created. 09:18:49 SQL> 09:18:49 SQL>alter session set nls_date_format = 'mm/dd/yy'; Session altered. 09:18:49 SQL> 09:18:49 SQL>insert into d values('12/10/1902'); 1 row created. 09:18:49 SQL>insert into d values('12/15/1902'); 1 row created. 09:18:49 SQL>insert into d values('12/12/1952'); 1 row created. 09:18:49 SQL>insert into d values('12/09/2002'); 1 row created. 09:18:49 SQL> 09:18:49 SQL>commit; Commit complete. 09:18:49 SQL> 09:18:49 SQL>select d, to_char(d,'mmrr') 09:18:49 2 from d 09:18:49 3 / D TO_C -------- ---- 12/10/02 1202 12/15/02 1202 12/12/52 1252 12/09/02 1202 4 rows selected. 09:18:49 SQL> 09:18:49 SQL>select * from d where d between '12/01/02' and '12/31/02'; D -------- 12/09/02 1 row selected. 09:18:49 SQL> 09:18:49 SQL>select * from d where to_char(d,'mmrr')='1202'; D -------- 12/10/02 12/15/02 12/09/02 3 rows selected. 09:18:49 SQL> 09:18:49 SQL>spool off 09:18:49 SQL> 09:18:49 SQL>set echo off 09:18:49 SQL> All explanations are in the fine manual. Jared Sony kristanto <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/14/2003 08:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Date data type Hi Listers, Does the date data type is not consistence on oracleDB ? I have the query below : Create table test (dt date, name varchar230)); SQL > Select * from test where dt between '01-DEC-02' and '31-DEC-02' - it returns 22 rows selected SQL > Select * from test where to_char(dt,'mmrr')='1202' - it returns 26 rows selected Why the last query return more rows selected than first query ? All comment would be appreciated. TIA, Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).