Re: NLS_DATE_FORMAT questions

From: Glen A Stromquist <glen_stromquist_at_nospam.yahoo.com>
Date: Mon, 29 Jul 2002 19:42:02 GMT
Message-ID: <eUg19.13345$Vj3.654535@news0.telusplanet.net>

Glen A Stromquist wrote:

> Sybrand Bakker wrote:
> 
>> On Mon, 29 Jul 2002 17:51:04 GMT, Glen A Stromquist
>> <glen_stromquist_at_nospam.yahoo.com> 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 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
>>>

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

OK tried adding the string NLS_DATE_FORMAT='dd-mon-yyyy' in to the HKLM/software/oracle in the registry, to no avail. Tried both 'dd-mon-yyyy' and 'dd-mon-rrrr', without and without single & double quotes. I am trying oracles ODBC test utility, connecting thru the odbc driver returns SQL_ERROR=-1 anytime the 4 digit year is used when querying one of the app's tables, with SQL_SUCCESS=0 anytime a 2 digit year is used. So the app's connection is still expecting a 2 digit year for some reason.

On the sqlplus side, after some more checking I see that the session parameter is set at dd-mon-rr, hence it's behaviour when querying. Received on Mon Jul 29 2002 - 14:42:02 CDT