Xref: alice comp.databases.oracle.tools:39469
From: johng@mm.com (TurkBear)
Newsgroups: comp.databases.oracle.tools
Subject: Re: PL/SQL ALTER SESSION SET Date format?
Date: Wed, 02 Feb 2000 22:02:47 GMT
Reply-To: See Message body for real address
Message-ID: <3898a82e.177623829@204.181.81.99>
References: <87a5o1$100$1@nnrp1.deja.com>
X-Newsreader: Forte Agent 1.5/32.451
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: 2 Feb 2000 15:56:22 -0600, 156.98.4.11
Lines: 59
X-Authenticated-User: turkbear
X-Report: Report abuse to abuse@newsfeeds.com
X-Abuse-Info: Please be sure to forward a copy of ALL headers, INCLUDING the body
X-Abuse-Info2: ALL Spam complaints are acted upon within 24 hours!
Organization: Newsfeeds.com http://www.newsfeeds.com 60,000+ UNCENSORED Newsgroups.
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!tank.news.pipex.net!pipex!feed3.newsfeeds.com!newsfeeds.com!goliath.newsfeeds.com!newsfeeds.com!156.98.4.11


The NLS_DATE_FORMAT change is not needed to get the result you want...

The DATE datatype format internally always includes the date/time info, so a
select to_char(sysdate,'fmMON DD,YYYY HHfm:MI:SS PM) 
should get the results you want into v_date - on my system, with a
NLS_DATE_FORMAT of DD-MON-YYYY, I get the following:

SQL> select to_char(sysdate,'fmMON DD,YYYY HHfm:MI:SS PM') from dual;

TO_CHAR(SYSDATE,'FMMONDD,YYYYHHFM:MI:SSPM')
---------------------------------------------------------------------------
FEB 2,2000 4:01:58 PM

SQL> 

hth,

> 
newopt@my-deja.com wrote:

>I want to change the default date format in a PL/SQL script to include
>both the date and time using the ALTER SESSION function. It's a real
>hassle to change the NLS_DATE_FORMAT in the init.ora file (you have to
>add the statement & then spin down the database to activate the change).
>
>Here's a PL/SQL script that should reset the default date format but
>fails.
>
>Any help would be appreciated!
>
>SQL> get setdateformat.sql
>  1  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'
>  2  declare
>  3  v_date varchar2(30);
>  4  BEGIN
>  5  select to_char(SYSDATE,'fmMon DD, YYYY HHfm:MI:SS PM') into v_date
>from dual;
>  6* END;
>SQL> /
>declare
>*
>ERROR at line 2:
>ORA-00922: missing or invalid option
>
>If you create a PL/SQL script with just the:
>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'
>statement it works fine, but introduce anything else after this
>statement & it errors out??
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.



  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
   http://www.newsfeeds.com       The Largest Usenet Servers in the World!
------== Over 73,000 Newsgroups - Including  Dedicated  Binaries Servers ==-----