Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> NLS_DATE_FORMAT questions


From: Glen A Stromquist <>
Date: Mon, 29 Jul 2002 17:51:04 GMT
Message-ID: <cgf19.11346$>

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

Original text of this message