How to set format of DATE?

We have a sizable project that we are porting from SQL Server 6.5 to
Oracle 8.0.5, and we are running into problems with date fields. SQL
like

INSERT INTO MyTable (Name, Title, DateOfHire)
VALUES ('Jason', 'Programmer/Analyst', '1998-07-05');

fails. It worked fine on SQL Server, and we thought we were coding
our system to be as portable as possible, because we knew the Oracle
port was coming eventually. Oracle, however, wants the date (by
deafult, I would assume) to be like '05-July-1998'. To retrofit our
code would be an enormous task, and we'd really rather not do that.
We can execute an ALTER SESSION statement with every connect to the
server, but that's not a very good solution either. How does one go
about getting Oracle to accept a date in a yyyy-mm-dd format
permanently?

Permanently? You cannot, as any user setting overrides the server setting. One
could set NLS_DATE_FORMAT on the server (any Unix user might simply override
it in .profile) but an ALTER SESSION would be the only fool proof option I
guess.

Note that the factory default date format uses MON, not MONTH, which implies
that Oracle would expect JUL, not JULY like you typed. However, as Oracle uses
some logic to try other formats when the given one fails, July is bound to be
accepted as well.

Arjan.

Hosted by www.Geocities.ws