Re: to_date function

"Katak" <kahtat_at_yahoo.com> wrote in message news:9vml6r$bhk$1_at_violet.singnet.com.sg...
> dear all
>
> I am having difficulty selecting records based on dates. Can anyone help?
>
> I tried using
> select * from tableA where created_date = to_date('15/12/01',
'dd/mm/yy');
>
> though i have records in the database. i do not get any results executing
> the above mentioned SQL code. anyone can lend a hand on this????? thanks

Does your date in a table have a time also? Something like '15/12/01 14:53:22'? If so, than you are comparing '15/12/01 14:53:22' with '15/12/01 00:00:00'. Try this:

select * from tableA where trunc(created_date) = to_date('15/12/01', 'dd/mm/yy');

Mind you, the SQL like this won't use index on the created_date fileld. You yould create function-based index on trunc(created_dt), or use query like this:

select * from tableA where created_date between to_date('15/12/01', 'dd/mm/yy') and to_date('15/12/01', 'dd/mm/yy')+1;

This will use index on created_date. Received on Tue Dec 18 2001 - 02:20:30 CST