Re: Alter session on NLS_DATE_FORMAT being ignored

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 25 Feb 2011 06:06:51 -0800 (PST)
Message-ID: <67aa3eaa-2bdb-48cf-8c6d-f9a9b3548bbc_at_8g2000prt.googlegroups.com>

On Feb 24, 9:05 am, Danmath <danmat..._at_gmail.com> wrote:
> I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch
> process right after the connection takes place. The format I specify
> is YYYYMMDDHH24MISS, this is the format used all over the process. In
> my development environment this works perfectly, but I've had problems
> in other environments.
>
> CASE A development environment: The process works fine, $NLS_LANG and
> $NLS_DATE_FORMAT environment variables are not set.
>
> CASE B Test envirnonment 1: The process failed.
>     $NLS_LANG=American_America.WE8ISO8859P1
>     $NLS_DATE_FORMAT environment variable is not set.
>       For some reason the $NLS_LANG variable seems to have more weight
> than the alter session command.. why?
>       The process works fine after setting $NLS_DATE_FORMAT to the
> desired format.
>
> CASE C Test envirnonment 2: The process failed. $NLS_LANG and
> $NLS_DATE_FORMAT environment variables are not set. Can't get it to
> work here. Any idea why?

My question to you is why are your relying on the nls_date format parameters at all? Every SQL statement that retrieves or passes in a date value should be written using to_char or to_date with the diresed/ expected format mask specified. Then your code is environment independent. After all can your guarantee that your code will always run in an enfironment with the nls_date set the way you expect.

IMHO -- Mark D Powell -- Received on Fri Feb 25 2011 - 08:06:51 CST