Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 8.0.5 and Date formats
A copy of this was sent to jlee.no.spam_at_rri-medtech.com (Jason Lee)
(if that email address didn't require changing)
On Tue, 02 Mar 1999 21:42:37 GMT, you wrote:
>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? Thanks.
>
in 8.0 and less you will have to use the ALTER SESSION command (why is that not a good solution?). relying on the nls_date_format init.ora parameter is *not* a good idea.
in 8.1 and above you are able to
create or replace trigger data_logon_trigger
after logon
ON DATABASE
begin
execute immediate
'alter session set nls_date_format = ''yyyy-mm-dd'' ';
end;
/
instead of at the session level.
>
>Jason Lee | 186,000 miles per second. It's not just a
>jlee_at_rri-medtech... | good idea. It's the law.
>(Please doctor the addy appropriately)
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities