Like I said previously, did you try this:
select * from test where dt between to_date('01-DEC-02 00:00:00','DD-MON-YY
HH24:MI:SS') and to_date('31-DEC-02 23:59:59','DD-MON-YY HH24:MI:SS');
Richard
-----Original Message-----
Sent: Wednesday, January 15, 2003 9:34 PM
To: Multiple recipients of list ORACLE-L
Thank you very much for all your respond.
It will be helpful, but Jared, I'm sure that my data ain't contains another
'02' year except 2002.
Rgrds,
Sony
> -----Original Message-----
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, January 16, 2003 12:14 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: Date data type
> Importance: High
>
> 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: 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: Richard Ji
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).
