Re: Problem with Where clause

On 03-Okt-98 00:06:01 Jeff McClure wrote:

>Folks,

[...]

>1)to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') <=
>to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') and
>to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') >= to_char(sysdate -
>.006944444,'MM/DD/YYYY HH24:MI:SS')

>.006944444 is the fractional (day) equivelant of 10 minutes...

>2)to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') between
>to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') and
>to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') >= to_char(sysdate -
>.006944444,'MM/DD/YYYY HH24:MI:SS')
[...]

Hello Jeff, why do you bother with all the to_char conversations? Try this

... where timestamp between sysdate-.006944444 and sysdate

This way you circumvent the ASCII sorting of the dates (there you must use 'YYYY/MM/DD HH24:MI:SS' to get the correct ordering) and you can speed things up using an index on the timestamp column. Whith to_char, Oracle won't use an index.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |