Date - Query with no rows returned -Strange !!!

Dear all,

I have come across this problem:

Database version: 8.1.7
Client: 9i

When I issue a query like below from the client:

select * from t
where date_col=to_date('2002/08/26','yyyy/mm/dd');

it returns no rows, although the records do exist.

The records only appear if I change query like this:

select * from t
where date_col>=to_date('2002/08/26','yyyy/mm/dd');

why is this so??? can someone explain this?

Is there any settings needed to be corrected?

Thanks in advance.

You're not respecting the time component of the date (ie. there are no records at midnight of your date). Try:

Code:

---

select * from t
where trunc(date_col)>=to_date('2002/08/26','yyyy/mm/dd');

---

One too many '>' characters in jeff's post. What he probbably realy ment was

Code:

---

select * from t
where trunc(date_col)=to_date('2002/08/26','yyyy/mm/dd');

---

Hi both,

Thanks for your reply.
However, I tried your codes, it doesn't seem to be working, and I don't think 'time' here is the real issue. Since by using to_date(date_col,'yyyy/mm/dd'), the time should be eliminated from the query (although it's still exist).

Any new thoughts? Thanks.

Sorry both...your answer is actually working...
Thanks for your great help!!