>> 4 WHERE to_char(jt_datetime,'DD-MON-YYYY HH24:MI:SS') >=
>> 5 (select to_char(ended_datetime,'DD-MON-YYYY HH24:MI:SS') from
This statement is actually comparing TWO STRINGS and NOT TWO DATES.
The STRING '23-JAN-2003 19:00:00' IS GREATER THAN the STRING
'23-APR-2003 21:30:00' Whereas the intention was to compare the dates
23rd January 2003 and 23rd of April 2003. If the format used in
the TO_CHAR() was 'YYYYMMDD HH24:MI:SS', it would have worked as the
strings themslves would be in chronological order as that of the dates.
-----Original Message-----
From: wan loo [mailto:[Email Address Removed] Wednesday, April 30, 2003 12:11 PM
To: LazyDBA.com Discussion
Subject: Queries returning different result sets
Hi DBAs,
There are two queries here that our team expected the same result.
But when we run them, the results are different. The first result is the
correct one.
1 SELECT
2 count(*)
3 FROM valu_daily_unit_values_jt
4 WHERE jt_datetime >=
5 (select ended_datetime from valu_flow_status
6 where institution_id like 'NYSE' and as_of_date = '25-APR-2003')
7* and jt_operation != 'DEL'
COUNT(*)
----------
283441
SQL> r
1 SELECT
2 count(*)
3 FROM valu_daily_unit_values_jt
4 WHERE to_char(jt_datetime,'DD-MON-YYYY HH24:MI:SS') >=
5 (select to_char(ended_datetime,'DD-MON-YYYY HH24:MI:SS') from
valu_flow_status
6 where institution_id like 'NYSE' and as_of_date = '25-APR-2003')
7* and jt_operation != 'DEL'
COUNT(*)
----------
1095276
What we think are the date format and to_char function. But what is the
explanation?
Pls advise. Thanks in advance.
Jeremy
_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page