Re: date format

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/08/02
Message-ID: <8m99dq$2414$1@s2.feed.news.oleane.net>#1/1

Do not confuse the internal date format and the format for the input/output (insert/select) operations.

The internal or storage format is associated with the date type.

The format for insert/select is used with the to_date and to_char function to convert a human date to an storage format and vice versa.

For instance:
insert into my_table (date_col) values(to_date('2000.08.02','YYYY.MM.DD')); select to_char(date_col,'YYYY.MM.DD') from my_table;

When you use:
select date_col from my_table;
Oracle use an implicit call to to_char function with your default date format.
You can see your default date format with the query: select value from nls_session_parameters where parameter='NLS_DATE_FORMAT';

Exemple:

v734> create table my_table (date_col date);

Table created.

v734> insert into my_table (date_col)
values(to_date('2000.08.02','YYYY.MM.DD'));

1 row created.

v734> select to_char(date_col,'YYYY.MM.DD') from my_table;

TO_CHAR(DATE_COL,'YYYY.MM.DD')

---

2000.08.02

1 row selected.

v734>
v734> select date_col from my_table;

DATE_COL

---

02/08/2000 00:00:00

1 row selected.

v734> select value from nls_session_parameters where parameter='NLS_DATE_FORMAT';

VALUE

---

DD/MM/YYYY HH24:MI:SS 1 row selected.

--
Have a nice day
Michel


Andreas Lommel <him_lommel_at_t-online.de> a écrit dans le message :
39881712.19300612_at_news.btx.dtag.de...


> How can I create a table with the date format YYYY.MM.DD ?

> drop table datest;

>

> create table datest

> (

>     lfdnr               number(7),

>     datum               date

> )

> tablespace data01

> storage

> (

>   minextents 1

>   maxextents unlimited

> )

> /

Received on Wed Aug 02 2000 - 00:00:00 CDT