Re: SQL to_date problem

Hi,

>I have 2 querys:
> and ba_erkezes=to_date('1998/09','YYYY/MM');
> and to_char(ba_erkezes,'YYYY/MM')='1998/09';

The time is also included in the date. So if you create a record with SYSDATE, the time is also important in your comparison.

>(I don't want to use the 2nd, because it does not use my index, and the bma
>has 80000 rows...)

That isn't large ;-)

Since the query above doesn't use the index either, you could:

1. Create another column (and index it) with the date without the time
2. Update your table so that it has only the date (or year-month) part
3. use the following construction:

and ba_erkezes between to_date('19980901000000', 'yyyymmddhh24miss') and to_date('19980930235959','yyyymmddhh24miss')

If your query always uses the last month, you better use the index in descending order. It will speed up your queries!

select /*+ INDEX_DESC(<the name of your index>) */ * from my_table
where ba_erkezes = ....

• Martin Jesterhoudt
• martinj(replace this by @)xs4all.nl

  ---