Re: Oracle-VB error message

You could lookup the error message in the Oracle Server Messages manual. If you don't have it, you can read it at http://technet.oracle.com You need to subscribe, but subscription is *free*

Your problem in this particular case is probably your default date format is different from what you expect it to be. This is arranged by setting the registry string value NLS_DATE_FORMAT under hklm\software\oracle.
If you don't set it the default is DD-MON-YY. You can verify what it is now by running select * from nls_session_parameters.
If you are running 8i you can easily set the date format by creating a logon trigger, which will issue
alter session set nls_date_format = 'DD/MM/YYYY'

However, it is good practice *not* to rely on any default and always explicitly use an explicit mask like this:

BETWEEN to_date('01/01/2000','DD/MM/YYYY' AND TO_DATE('09/01/2000','DD/MM/YYYY') You will probably still reach better results if you change the second half in
TO_DATE('10/01/2000','DD/MM/YYYY') - 1/3600 /*one second*/

In that case you won't need the trunc, and if there is an index on the date column, your sql will use it.

Hth,

Sybrand Bakker, Oracle DBA

<pac3_at_voicenet.com> wrote in message news:8ptr9j$eev$1_at_nnrp1.deja.com...
> Hi,
> I'm having a problem getting Oracle to process the following SQL
> string through VB.
>
> SELECT * FROM DICT_DBA.ACF WHERE ct_Protocol IN
> ('29060/676','29060/688','104864/435') AND TRUNC(FIRST_FAIL_DATE)
> BETWEEN '01/01/2000' AND '09/01/2000' ORDER BY ct_Protocol,
> FIRST_FAIL_DATE desc
>
> When I run it I get an ODBC error:
>
> 22008:[Microsoft][ODBC Driver for Oracle][Oracle]ORA-01843: not a valid
> month
>
> Is there an accessible Knowledge Base for Oracle on-line? I went to
> www.oracle.com and found pay help, but nothing like a KB.
>
> My experience lies primarily in SQ server. With the exception of the
> TRUNC function, this query would run. Can anyone help me with this?
>
>
> Thanks in advance!
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Sep 15 2000 - 14:49:02 CDT