Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> NLS_DATE_FORMAT questions
In one of our app's I am getting an Ora-3146 error when it passes the date
to a stored procedure in dd-mon-yyyy format, The odbc trace file shows an
"ora-01830:date format picture ends before converting entire input string"
error, which means as far as I can tell the system is expecting a 2 digit
year rather than the 4 digit year it recieves.
The perplexing thing is that with an older oracle ODBC (816) it was ok, but
the 817 driver returns this error. The database nls_date_format shows
dd-mon-yyyy as it is defined in the init.ora file, but if I select sysdate
from dual I get a 2 digit year, and any query using 4 digit years returns
the ora-01830 error unless I run an alter session set nls_date_format
command.
A query using a 2 digit year returns correct results, but I thought it
would be treated as 19xx unless the nls_date_format was dd-mon-rrrr!
Strangely enough I connected thru sqlplus on a linux box and the opposite occured, select sysdate from dual returned a 4 digit year and using the dd-mon-yyyy format in queries works correctly, and using a 2 digit year works as I expect, treating it as a 19xx year and returning no data.
Why is the SQLPLUS on windows 2000 not returning the correct result? I am assuming that the same problem is causing the application to fail and return the ODBC call failed error.
Since this app is due to be replaced in the near future, I want to try and find a quick way to correct this, right now I'm thinking changing the init.ora parameter to the rrrr in place of the yyyy in nls_date_format, which if I'm correct will treat either 2 or 4 digit years correctly.
The other options are changing all of the application code to send a
to_date in front of the date string, or adding an alter session set
nls_date_format to the affected procedures. I think that changing the
affected procedures would be less onerous, if so will a simple alter
session statement at the start of the procedure (before or after
"begin"?)do the job?
TIA Received on Mon Jul 29 2002 - 12:51:04 CDT