Re: SQL to_date problem

M,

     You are comparing two specific dates rather than two months. TO_DATE('1998/09','YYYY/MM') will convert to midnight of Sept 1, 1998. In order to continue using the index your query should appear as:

SELECT DISTINCT 0
  FROM boltok, bma

 WHERE ba_bjkod = bj_kod
   AND ba_erkezes >= to_date('1998/09', 'YYYY/MM')
   AND ba_erkezes <  to date('1998/10', 'YYYY/MM');

...or if you only want to pass in one variable, you could try something like:

SELECT DISTINCT 0
  FROM boltok, bma

 WHERE ba_bjkod = bj_kod
   AND ba_erkezes >= to_date('1998/09', 'YYYY/MM')
   AND ba_erkezes <  LAST_DAY(to date('1998/09', 'YYYY/MM')) + 1;

Jay!!!

MGperY wrote:

> Hi there !
>
> I have 2 querys:
> select distinct 0 from boltok, bma
> where ba_bjkod=bj_kod
> and ba_erkezes=to_date('1998/09','YYYY/MM');
> -------------------------------------------------
> select distinct 0 from boltok, bma
> where ba_bjkod=bj_kod
> and to_char(ba_erkezes,'YYYY/MM')='1998/09';
>
> the 1st give me no rows, the 2nd give me 1 row, wich is the correct answer.
> I think these two must have the same result, but somethings is be wrong with
> the format mask.
> Can you help me ?
>
> (I don't want to use the 2nd, because it does not use my index, and the bma
> has 80000 rows...)
>
> thanx
>
> M.
Received on Thu Oct 22 1998 - 13:39:05 CDT