Re: help with date format !!!! urgent

On Jun 12, 3:48 am, News <Contact_..._at_hotmail.com> wrote:
> in ini.ora:
> NLS_DATE_FORMAT = YYYY-MM-DD
>
> SQL> show parameter nls_date_format
> nls_date_format string YYYY-MM-DD
>
> SQL> CREATE TABLE HR.T (D DATE DEFAULT '0001-01-01' NULL);
>
> ORA-01847: day of month must be between 1 and last day of month
>
> SQL> alter session set nls_date_format="YYYY-MM-DD";
>
> Session altered.
>
> SQL> CREATE TABLE HR.T (D DATE DEFAULT '0001-01-01' NULL);
>
> Table created.
>
> changing nls_date_format has effect in session and not in system
> level.
>
> Please help ! i cannot import full database because of this problem.
>
> SQL> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Release 9.2.0.1.0 - 64bit Production
> PL/SQL Release 9.2.0.1.0 - Production
> CORE 9.2.0.1.0 Production
> TNS for IBM/AIX RISC System/6000: Version 9.2.0.1.0 - Production
> NLSRTL Version 9.2.0.1.0 - Production

The first 'problem' you have is apparently of your own doing, that being a reliance on 'default' date display formats. To create tables which depend upon a certain 'default' display format is the surest recipe for failure, as you've now discovered; this is your second 'problem'.

The only sure way to fix this is to recreate your tables with proper DEFAULT declarations:

CREATE TABLE HR.T (D DATE DEFAULT to_date('0001-01-01','YYYY-MM- DD') NULL); This will remove any ambiguity, and prevent you from having to implement any triggers like you've just done.

Proper design and implementation are paramount.

David Fitzjarrell Received on Tue Jun 12 2007 - 10:20:24 CDT