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: Glen A Stromquist <>
Date: Mon, 29 Jul 2002 18:58:54 GMT
Message-ID: <Ofg19.11472$>

Sybrand Bakker wrote:

> 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\oracleHKLM\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.
> Hth
> Sybrand Bakker, Senior Oracle DBA
> To reply remove -verwijderdit from my e-mail address

thanks Sybrand - I did try adding that string variable, but I put it in the HOME0 folder under HKLM\software\oracle and it stopped the ODBC from connecting at all. I'll try what you said and post the results back.

btw - any idea why sqlplus in windows acts the way I described, and sqlplus in linux seems to work as it should?

TIA Received on Mon Jul 29 2002 - 13:58:54 CDT

Original text of this message