Hi oracle folks,

I have done the following insert statement:

the datatype of datum_upd is DATE.

SQL> insert into manser.previsions (cell,datum_upd,idnr) values
2 (44,TO_DATE ('15-JUL-2003 1:00','DD-MON-YYYY HH24:MI'),60390);

1 row created.

when i query it the date format is not what i expect .

SQL> select datum_upd from manser.previsions where cell = 44;

DATUM_UPD
---------
15-JUL-03

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.0.0 - Production


why is the query result not in the wanted form ?
i would expect the following query result:

DATUM_UPD
---------
15-JUL-2003 1:00

can anyone give me some explanations ?
i am running oracle 8.1.7 under openVMS alpha 7.3.
great thanks in advance.

Nazim Manser

The date is fine. Dates are stored in an internal format in Oracle and when
you look at them in SQLPlus they are formatted into a string representation
of a date. They are formatted according to a default nls date format
setting. So what you are seeing is the default format of the date. To
specify how you want to display it use to_char just as you used to_date.

eg
select to_char(sysdate,'mm/dd/yyyy')||' '||to_char(sysdate,'yyyymmdd
hh24:mi:ss') from dual;
will return the current date and time formatted 2 different ways. Both are
correct, it is just the string representation of the date.
Jim

--
Replace part of the email address: kennedy-down_with_spammers (AT) attbi (DOT) com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Manser" <nmanser (AT) progis (DOT) de> wrote

Originally posted by Manser
want the format defined in NLS_DATE_FORMAT, which by default is
DD-MON-RR. You can change that default like this:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI';

Or you can be explicit in your select:

SQL> select to_char(datum_upd,'DD-MON-YYYY HH24:MI') d from
SQL> manser.previsions where cell = 44;

D
---------------
15-JUL-03 01:00

The key point is that dates are not stored as a formatted character
string, they are stored in some internal coding.

--
Posted via http://dbforums.com