Re: PL/SQL ALTER SESSION SET Date format?

Thanks for the reply, but the problem is that the script I am actually using (not the one i posted) retrieves a column from an Oracle table that includes both date & time. If the session date format is not reset to include both date & time my script variable only pulls in the date?

In article <3898a82e.177623829_at_204.181.81.99>,   See Message body for real address wrote:
>
> 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_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 ==-----


>



Sent via Deja.com http://www.deja.com/
Before you buy.