"Glen A Stromquist" <glen_stromquist_at_nospam.yahoo.com> wrote in message
news:cgf19.11346$Vj3.646644_at_news0.telusplanet.net...
> 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
>
I would strongly recommend always using an explicit date mask, as defensive
coding. Don't rely on defaults.
Bite the bullet, and change the code now, is my recommendation.
Paul
Received on Mon Jul 29 2002 - 15:52:54 CDT
