Re: TO_DATE question in insert

From: andrewst <member14183_at_dbforums.com>
Date: Wed, 16 Jul 2003 12:40:24 +0000
Message-ID: <3116673.1058359224@dbforums.com>

Originally posted by Manser
> 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

If you don't specify the format you want to see, SQL Plus assumes you 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

Received on Wed Jul 16 2003 - 07:40:24 CDT