Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: help: to_date function

Re: help: to_date function

From: Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl>
Date: Mon, 28 Jan 2002 18:06:59 GMT
Message-ID: <3c55911a.1789875@news.freeler.nl>


I'm a bit puzzled: to_char can only be used on a NUMBER or a DATE type field, so

to_char('2002-01-20 23:59:59')

should be syntactically incorrect, except maybe when your default date format is 'yyyy-mm-dd hh24:mi:ss'. In that case it is possible that Oracle can interpret the string '2002-01-20 23:59:59' as something it can and will convert to DATE type using the dafult date format, performs the to_char function on it, thereby converting it to VARCHAR2 again (so you are where you started). If you then apply the to_date function, the VARCHAR2 field is converted to to DATE again.

Regarding your question: how did you conclude that in one case a full table scan is used and in the other the index?

Jaap.

On Mon, 28 Jan 2002 16:04:51 GMT,
u518615722_at_spawnkill.ip-mobilphone.net (Mike F) wrote:

>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
Received on Mon Jan 28 2002 - 12:06:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US