help: to_date function

What is the correct way to use to_date function?

In some case, when I use

select (*) from test where
time > to_date ('2002-01-20 23:59:59','yyyy-mm-dd hh24:mi:ss')

and
time < to_date ('2002-01-24 23:59:59','yyyy-mm-ddhh24:mi:ss');

will cause a full table scan, I have to change it to

select (*) from test where
time > to_date (to_char('2002-01-20 23:59:59'),'yyyy-mm-dd hh24:mi:ss') and
time < to_date (to_char('2002-01-24 23:59:59'),'yyyy-mm-ddhh24:mi:ss');

in order for the index to be used.

In some cases,use
to_date('2002-01-24 23:59:59','yyyy-mmddhh24:mi:ss')  will be better than
to_date (to_char('2002-01-24 23:59:59'),'yyyy-mm-ddhh24:mi:ss');

Does anybody know why?

Thanks for you help  

-- 
Sent  by dbadba62 from  hotmail in area  com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new