Re: PL/SQL ALTER SESSION SET Date format?

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')

>

newopt_at_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 ==----- Received on Wed Feb 02 2000 - 23:02:47 CET