Re: Date data type
My guess is that your test data contains two dates with the year 1902.
09:18:49 SQL
09:18:49 SQLcreate table d( d date );
Table created.
09:18:49 SQL
09:18:49 SQLalter session set nls_date_format = 'mm/dd/yy';
Session altered.
09:18:49 SQL
09:18:49 SQLinsert into d values('12/10/1902');
1 row created.
09:18:49 SQLinsert into d values('12/15/1902');
1 row created.
09:18:49 SQLinsert into d values('12/12/1952');
1 row created.
09:18:49 SQLinsert into d values('12/09/2002');
1 row created.
09:18:49 SQL
09:18:49 SQLcommit;
Commit complete.
09:18:49 SQL
09:18:49 SQLselect d, to_char(d,'mmrr')
09:18:49 2 from d
09:18:49 3 /
DTO_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 SQLselect * 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 SQLselect * 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 SQLspool off
09:18:49 SQL
09:18:49 SQLset 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).
RE: Date data type
Title: RE: Date data type Because in the second select, you are using the wrong mask. Try 'mmyy'. -Original Message- From: Sony kristanto [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 8:49 PM To: Multiple recipients of list ORACLE-L 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).
RE: Date data type
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 SQLcreate table d( d date );
Table created.
09:18:49 SQL
09:18:49 SQLalter session set nls_date_format = 'mm/dd/yy';
Session altered.
09:18:49 SQL
09:18:49 SQLinsert into d values('12/10/1902');
1 row created.
09:18:49 SQLinsert into d values('12/15/1902');
1 row created.
09:18:49 SQLinsert into d values('12/12/1952');
1 row created.
09:18:49 SQLinsert into d values('12/09/2002');
1 row created.
09:18:49 SQL
09:18:49 SQLcommit;
Commit complete.
09:18:49 SQL
09:18:49 SQLselect d, to_char(d,'mmrr')
09:18:49 2 from d
09:18:49 3 /
DTO_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 SQLselect * 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 SQLselect * 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 SQLspool off
09:18:49 SQL
09:18:49 SQLset 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).
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: [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 SQLcreate table d( d date );
Table created.
09:18:49 SQL
09:18:49 SQLalter session set nls_date_format = 'mm/dd/yy';
Session altered.
09:18:49 SQL
09:18:49 SQLinsert into d values('12/10/1902');
1 row created.
09:18:49 SQLinsert into d values('12/15/1902');
1 row created.
09:18:49 SQLinsert into d values('12/12/1952');
1 row created.
09:18:49 SQLinsert into d values('12/09/2002');
1 row created.
09:18:49 SQL
09:18:49 SQLcommit;
Commit complete.
09:18:49 SQL
09:18:49 SQLselect d, to_char(d,'mmrr')
09:18:49 2 from d
09:18:49 3 /
DTO_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 SQLselect * 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 SQLselect * 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 SQLspool off
09:18:49 SQL
09:18:49 SQLset 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).
Re: Date data type
On Wednesday 15 January 2003 18:34, Sony kristanto wrote:
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.
Maybe not, but I suggest you look up RR in the fine manual anyway. :)
Jared
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 SQLcreate table d( d date );
Table created.
09:18:49 SQL
09:18:49 SQLalter session set nls_date_format = 'mm/dd/yy';
Session altered.
09:18:49 SQL
09:18:49 SQLinsert into d values('12/10/1902');
1 row created.
09:18:49 SQLinsert into d values('12/15/1902');
1 row created.
09:18:49 SQLinsert into d values('12/12/1952');
1 row created.
09:18:49 SQLinsert into d values('12/09/2002');
1 row created.
09:18:49 SQL
09:18:49 SQLcommit;
Commit complete.
09:18:49 SQL
09:18:49 SQLselect d, to_char(d,'mmrr')
09:18:49 2 from d
09:18:49 3 /
DTO_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 SQLselect * 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 SQLselect * 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 SQLspool off
09:18:49 SQL
09:18:49 SQLset 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: Jared Still
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).
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).
RE: Date data type
Your between is the same as: where dt = '01-DEC-02' and dt = '31-DEC-02' and since '31-DEC-02' is '31-DEC-02 00:00:00', you are excluding records after '31-DEC-02 00:00:00'. Hence less records are returned. -Original Message- Sent: Tuesday, January 14, 2003 11:49 PM To: Multiple recipients of list ORACLE-L 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: 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).
