Re: strange date insertion

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Mon, 29 Mar 2004 13:22:03 GMT
Message-ID: <%pV9c.130302$Wa.3714@news-server.bigpond.net.au>

"Andre" <avanrossem_at_hotmail.com> wrote in message news:4d32d1be.0403290414.1f94c80a_at_posting.google.com...
> Hello all,
>
> I have a problem:
>
> If i put in the following value in an oracle date-field:
> 01-01-29 then oracle converts it to 01-01-2029
>
> But If i insert 01-01-30 then oracle converts it to 01-01-1930
>
> Is this normal? Where can I change it?
>
> regards,
> Anneke

Anneke,

What version of Oracle are you using?
What does you INSERT statement look like? What is the value of your session parameter, 'nls_date_format'? Do the following SQL:
SELECT * FROM v$nls_parameters;

According to the "Oracle 9i SQL Reference" (pp.2-73 to 2-75), the RR format does something similar but would convert 01-01-30 to 01-01-2030 but would convert 01-01-50 to 01-01-1950 for current years up to 2049.

I suspect you may have a trigger left over the Y2K conversion which is doing the conversion for you. Do a search on ALL_TRIGGERS as follows: SELECT * FROM all_triggers WHERE table_name = 'YOUR_TABLE' AND table_owner = 'YOUR_SCHEMA' AND column_name = 'YOUR_DATE_COL';

Douglas Hawthorne Received on Mon Mar 29 2004 - 07:22:03 CST