RE: Date data type

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: Jared.Still_at_radisys.com [SMTP:Jared.Still_at_radisys.com]
> Sent: Thursday, January 16, 2003 12:14 AM
> To: ORACLE-L_at_fatcity.com
> Cc: Sony_at_polyfincanggih.com
> 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 <Sony_at_polyfincanggih.com>
> Sent by: root_at_fatcity.com
> 01/14/2003 08:48 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> 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: Sony_at_polyfincanggih.com
>
> 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: ListGuru_at_fatcity.com (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: Sony_at_polyfincanggih.com

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: ListGuru_at_fatcity.com (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: richard.ji_at_mobilespring.com

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: ListGuru_at_fatcity.com (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).