Re: configuring Oracle DATE dataype to accept ANSI SQL format

A better way is to use to_date and not rely on the client side nls_date_format setting.
eg
insert into t values(to_date('2001-12-13','yyyy-mm-dd'));

or on the client specify the nls_date_format you want instead of the default (which you can change on the server in the init.ora file; you have noticed that the default for the default is as you say, but it is configurable).

Relying on a specific string format for a date without specify the format is asking for trouble. Why? A client machine could change the nls_date_format setting on you and that would mess up your data.

Jim
"Neil Zanella" <nzanella_at_cs.mun.ca> wrote in message news:Pine.LNX.4.44.0203302155100.19990-100000_at_garfield.cs.mun.ca...
>
> Hello,
>
> Is it possible to configure the DATE datatype to accept
> ANSI SQL format under Oracle 8i? The default recognized
> format is YYYY-MMM-DD as can be seen below:
>
> SQL> CREATE TABLE T (d DATE);
>
> Table created.
>
> SQL> INSERT INTO T VALUES ('13-DEC-2001');
>
> 1 row created.
>
> SQL> INSERT INTO T VALUES ('2001-12-13');
> INSERT INTO T VALUES ('2001-12-13')
> *
> ERROR at line 1:
> ORA-01861: literal does not match format string
>
> SQL>
>
> If anyone knows how Oracle can be configured to
> accept ANSI SQL date format (same as ISO 8601)
> then please let me know.
>
> Thanks,
>
> Neil
>
Received on Sat Mar 30 2002 - 21:10:49 CST