Re: DAte conversion

From: Ron Reidy <rereidy_at_indra.com>
Date: Tue, 27 Nov 2001 17:32:57 -0700
Message-ID: <3C0430B9.C409227E@indra.com>

longinus wrote:
>
> how can i store the date in this format: 12.03.2001?
>
> Ron Reidy schrieb:
> >
> > longinus wrote:
> > >
> > > Becase the date type doesn't allow to store dates in 'DD.MM.YYYY'
> > > format,e.g. '12.03.2001'.
> > >
> > > Damien Salvador schrieb:
> > > >
> > > > On Tue, 27 Nov 2001 22:56:08 +0100, longinus
> > > > <longinus_at_zeus.polsl.gliwice.pl> a écrit:
> > > > >Date is stored in 'VARCHAR' field.
> > > >
> > > > >
> > > > >I want to convert end_date (VARCHAR) for date and choose the oldest
> > > > >one..
> > > > >SQL> select min(to_date(end_date,'DD.MM.YYYY'))
> > > > >reached';
> > > > >ERROR:
> > > > >ORA-01839: date not valid for month specified
> > > > >
> > > > >What is wrong?????
> > > >
> > > > Are you sure ALL your dates are in the DD.MM.YYYY form ?
> > > > Isn't there a MM.DD.YYYY date ? Maybe there is an invalid
> > > > date in your column.
> > > >
> > > > Why don't you store your dates in a date column ?
> > > >
> > > > --
> > > > Damien
> > Wrong! Dates are **not** a VARCHAR type - they are numbers stored in
> > the column. That is why the TO_DATE() function allows a picture clause
> > - to format the output.
> > --
> > Ron Reidy
> > Oracle DBA
> > Reidy Consulting, L.L.C.

You do not store a date in a format that is (very) human readable. As I said in my earlier post, the DATE datatype is a NUMERIC type. The internal representation is number of seconds from 4000 B.C. (I think, not too sure about the starting time). To get a date out in the format you want, you use the TO-CHAR() function. Consider the following example:

SQL*Plus: Release 9.0.1.0.0 - Production on Tue Nov 27 17:31:15 2001

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option
JServer Release 9.0.1.0.0 - Production

SQL> select to_char(sysdate, 'dd.mm.yyyy') now from dual;

NOW

27.11.2001

Read the concepts manual. Do a search for the DATE datatype and read what it is (for that matter, read about all basic data types - CHAR, VARCHAR, DATE, NUMBER). After you do this, you will understand more. Also, read about the TO_DATE() and TO_CHAR() functions, including picture formats and how they work.

Good luck!

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.

Received on Tue Nov 27 2001 - 18:32:57 CST