Oracle-L: Re: Date data type

From: <Jared.Still_at_radisys.com>
Date: Wed, 15 Jan 2003 09:18:45 -0800
Message-ID: <F001.0053069B.20030115091845@fatcity.com>

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';

12/09/02

1 row selected.

09:18:49 SQL>
09:18:49 SQL>select * from d where to_char(d,'mmrr')='1202';

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:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------