true, sorry
Ron Reidy schrieb:
>
> 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
>
> (c) Copyright 2001 Oracle Corporation. All rights reserved.
>
> 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 Wed Nov 28 2001 - 11:50:15 CST
