NLS_DATE_FORMAT and Y2K

From: Rich Goldkamp <RJGoldka_at_dcss.com>
Date: Thu, 16 Sep 1999 11:35:20 GMT
Message-ID: <7rqkln$5fi$1@nnrp1.deja.com>

Can the NLS_DATE_FORMAT parameter be set at the instance level???

When I execute the following query, it returns an error when run against an instance where the NLS_DATE_FORMAT parameter has been set.

SELECT	TO_DATE( DECODE( dummy, 'N', 'X',
			TO_DATE( '29-FEB-2000', 'DD-MON-YYYY' ) ) )
FROM	DUAL;

ERROR at line 1:
ORA-01839: date not valid for month specified

If I execute,

ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY"; then my above date query correctly returns this:

TO_DATE(DEC

---

29-FEB-2000 However, if I add a line to my parameter file like this:

NLS_DATE_FORMAT="DD-MON-YYYY" and bounce my instance, I still get the above listed ORA-01839 error. If I query V$PARAMETER, the server reports that the parameter is set (I've tried double, single, and no quotes around the date format specifier). The error is only corrected when I set the parameter at the session level (or with an environment variable at the desktop level). The obvious problem is that I don't want to have to implement the solution at the session or desktop level.

Has anyone found a way to set NLS_DATE_FORMAT at the instance level, and have it work correctly?

Thanks in Advance,
Rich
--

Rich Goldkamp, Consultant, OCP		RJGoldka_at_dcss.com
Digital Consulting & Software Services	(504) 523-5005 x2736

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 16 1999 - 06:35:20 CDT