Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: NLS_DATE_FORMAT questions

From: Sybrand Bakker <>
Date: Mon, 29 Jul 2002 20:28:59 +0200
Message-ID: <>

On Mon, 29 Jul 2002 17:51:04 GMT, Glen A Stromquist <> wrote:

>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
>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?

Changing init.ora is not going to help.
Your problem is at the client side, on session level. You need to set NLS_DATE_FORMAT in the win2k registry a string variable in HKLM\software\oracle
The best solution though is always use an explicit date mask in all your code and stop relying on the client's setting. I had a complete export failing once because of this. The developers didn't use a date mask on the default clause of a column definition. The nls_date_format on the server was different from that on the client. This was the table all other tables were referring to.


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jul 29 2002 - 13:28:59 CDT

Original text of this message