Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: help with date format !!!! urgent

Re: help with date format !!!! urgent

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Wed, 13 Jun 2007 14:48:04 -0700
Message-ID: <1181771284.006683.263120@i38g2000prf.googlegroups.com>


On Jun 13, 5:19 pm, News <Contact_..._at_hotmail.com> wrote:
> On 13 juin, 00:50, sybra..._at_hccnet.nl wrote:
>
>
>
>
>
> > On Tue, 12 Jun 2007 12:12:06 -0700, News <Contact_..._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.
>
> I dont know in which manuals you picked up this. This is what all I
> found
>
> NLS_DATE_FORMAT specifies the default date format to use with the
> TO_CHAR and TO_DATE functions. The default value of this parameter is
> determined by NLS_TERRITORY.
>
> The value of this parameter can be any valid date format mask, and the
> value must be surrounded by double quotation marks. For example:
>
> NLS_DATE_FORMAT = "MM/DD/YYYY"- Hide quoted text -
>
> - Show quoted text -

Typically my scripts to invoke the import utility look like this

#!/bin/ksh
. /please/dot/in_some_file_to_set_environment_for_oracle export NLS_LANG='some value'

/* example export NLS_LAND='AMERICAN_AMERICA.WE8ISO8859P1' */

Then you code the imp executable invocation.

The NLS_LANG will set the language and territory stuff which will then cascade down to default date formats etc Received on Wed Jun 13 2007 - 16:48:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US