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

[coolmandba]

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.

[marist89]

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

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

[jmodic]

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

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

[coolmandba]

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.

[coolmandba]

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