Re: SQL to_date problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 22 Oct 1998 21:04:39 GMT
Message-ID: <363c9d3d.32425765@192.86.155.100>

A copy of this was sent to Johan Ur Riise <riise_at_bgnett.no> (if that email address didn't require changing) On 22 Oct 1998 22:30:19 +0200, you wrote:

>> 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'))
>
>or use the trunc function
>
>... and trunc(ba_erkezes,'YYYY/MM') = to_date('1998/09','YYYY/MM') ...
>

but that would not satisfy the initial requirement:

   >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...) 
   >

if you use trunc() you won't be using the index....

and it would be trunc( ba_erkezes, 'mon' ), not with a format...

SQL> select trunc( sysdate, 'yyyy/mm' ) from dual; select trunc( sysdate, 'yyyy/mm' ) from dual

                       *

ERROR at line 1:
ORA-01898: too many precision specifiers

SQL> select trunc( sysdate, 'mon' ) from dual;

TRUNC(SYS

---

01-OCT-98
>--
>name: Johan Ur Riise
>org: unic consulting
>email: riise_at_bgnett.no
>mob: +47 90 15 77 78
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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 - 16:04:39 CDT