Re: help with date format !!!! urgent

On Tue, 12 Jun 2007 12:12:06 -0700, News <Contact_404_at_hotmail.com> wrote:

>On 12 juin, 20:44, sybra..._at_hccnet.nl wrote:
>> On Tue, 12 Jun 2007 09:47:04 -0700, News <Contact_..._at_hotmail.com>
>> wrote:
>>
>>
>>
>>
>>
>> >On 12 juin, 17:20, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>> >> On Jun 12, 3:48 am, News <Contact_..._at_hotmail.com> wrote:
>>
>> >> 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);
>>
>> >Thanks ! but ..
>> >it's not my database !! I'm trying to clone Oracle Applications HR
>> >module database.
>>
>> >The Tables are created this way by Oracle !!!!!!
>>
>> >and the problem remains : why altering nls-date-format works in
>> >session level and not in system level in ini.ora ?
>>
>> Which problem? Something which is working as designed?
>
>you mean nls-date-format in ini.ora is designed so .. to have no
>effect ?

I know you can't be bothered with reading manuals. If you would have done so, this entire thread would have been redundant. However, nls_date_format in init<sid>.ora (which you should stop using in 9i and higher, and replace by a spfile) applies to code (stored procedures) running on the server.
This setting is *always* overridden by the session NLS_DATE_FORMAT on the client. As different clients can use different settings you shouldn't rely on a default. You should always specify a format mask. And IIRC NLS_DATE_FORMAT can be set as an env var prior to imp.

-- 
Sybrand Bakker
Senior Oracle DBA