| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL to_date problem
A copy of this was sent to "MGperY" <mgyuri_at_freemail.c3.hu>
(if that email address didn't require changing)
On Thu, 22 Oct 1998 19:09:30 +0200, you 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';
>
well, ba_erkezes must not be the first day of the month then or maybe it has a time component. Oracle dates have day, month, year, hour, minute, second in them. the first one, compare date to date, will fail if the date in the database is not the 1'st day of the month at MIDNIGHT.
If you are trying to find the records for september, try a query like:
select ...
from boltok, bma
where ba_bjkod= bj_kod
and ba_erkezes
BETWEEN to_date('1998/09','YYYY/MM')
and last_day( to_date('09/1998 23:59:59','mm/yyyy hh24:mi:ss'))
that'll use the index and get all dates in 09/1998
>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.
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Oct 22 1998 - 13:53:25 CDT
 |
 |