Re: date format for 9i

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Sun, 21 Mar 2004 03:57:08 GMT
Message-ID: <oo87c.118116$Wa.207@news-server.bigpond.net.au>

"George D. Benetos" <gbenet_at_intracom.gr> wrote in message news:c398v0$kue$1_at_newsserv.intranet.gr...
> I get the following result from sql plus
>
> 'yyyy-mm-dd hh24:mi:ss.0'
>
> instead of
>
> 'yyyy-mm-dd hh24:mi:ss'
>
> when I try to retrieve a culumn DATE .
> The code is the same , only change from 8i to 9i .
>
>
> Any ideas ?
>
> thanx .....
>
>
>

George,

When you issue the following query on both 8i and 9i, what results do you get?
SELECT * FROM nls_session_parameters;

The format of the date type does not appear to correspond to what I get after I set NLS_TERRITORY='GREECE' on 9.2.0.1 on WinXP.

PARAMETER                      VALUE
------------------------------ --------------------------
NLS_LANGUAGE                   ENGLISH
NLS_TERRITORY                  GREECE
NLS_CURRENCY                   Ç
NLS_ISO_CURRENCY               GREECE
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              ENGLISH
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Ç
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

You may want to review "NLS_DATE_FORMAT" at http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3648038241603 to see if this addresses your problem.

There may be a login.sql or a glogin.sql that is setting the NLS_DATE_FORMAT for you as you enter SQL*Plus.

Douglas Hawthorne Received on Sat Mar 20 2004 - 21:57:08 CST