| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: compare round(date)s
A copy of this was sent to zlotchen_at_voyager.rtd.utk.edu (David Zlotchenko)
(if that email address didn't require changing)
On 26 Dec 1999 15:43:09 GMT, you wrote:
>Hi,
>
>I a puzzled by behavior of my Oracle 8.0.4.
>
>I have table with field DATE field axtime. Here are two queries that
>return different results:
>
>SQL> select count(*) from atable partition(PART19991121)
> 2 where round(axtime) <
>round(to_date('1999/11/22','YYYY/MM/DD'));
>
> COUNT(*)
>----------
> 9648
>
>SQL> select count(*) from atable where axtime <
>round(to_date('1999/11/22','YYYY/MM/DD'));
>
> COUNT(*)
>----------
> 53565
>
>What makes these two queries to return different counts?
>
for 1 -- they are querying different sets of data.
select count(*) from atable partition(PART19991121) select count(*) from atable -- no partition
For another, round rounds -- it does not just strip off the time (that would be TRUNC). Consider:
tkyte_at_8.0> select count(*) from all_users where round(created) < created;
COUNT(*)
19605
tkyte_at_8.0> c/</>
1* select count(*) from all_users where round(created) > created
tkyte_at_8.0> /
COUNT(*)
6215
If something happens after noon, it gets rounded UP.
The 2 predicates:
> 2 where round(axtime) <
>round(to_date('1999/11/22','YYYY/MM/DD'));
> where axtime <
>round(to_date('1999/11/22','YYYY/MM/DD'));
are very different. sometimes axtime rounds UP, sometimes DOWN.
>Here is my sessions' setting for date:
>SQL> select round(to_date('1999/11/22','YYYY/MM/DD')) from dual;
>
>ROUND(TO_DATE('1999/
>--------------------
>22-Nov-1999 00:00:00
>
>As far as I can see, round() works as I would expect making the date
>at 0 am.
>
the time on the TO_DATE( '1999/11/22', 'YYYY/MM/DD' ) was already 0am since that is the default. the round was 'redundant' in this case.
with or without the round it is '0am'
tkyte_at_8.0> select round(to_date('1999/11/22','YYYY/MM/DD')) from dual 2 /
ROUND(TO_DATE('1999/
tkyte_at_8.0> select to_date('1999/11/22','YYYY/MM/DD') from dual 2 /
TO_DATE('1999/11/22'
tkyte_at_8.0>
Try TRUNC() if you want to strip the time component off of a date.
>Any suggestion will be appreciated. David.
>--
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Dec 26 1999 - 10:17:50 CST
 |
 |