Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Date functions

Re: Date functions

From: Oliver Otto <ootto_at_genese.de>
Date: Tue, 20 Aug 2002 12:53:43 +0200
Message-ID: <3D621FB7.F60EE0BF@genese.de>


Hallo,

thanx to both of you for your quick answer. But I still do not understand the thing. I expect that it might has something to do with the date format but not at all. Our databse is set to 'yyyy-mm-dd' as the main date format. Beside this I set the session nls_date_format to the same value to be sure also on the client side. In fact of this I think this is ok issn't it ..?

What I still do not understand is that if I do this:

    select benutzer, round(anlagedatum) from zeit_table where round(sysdate)=round(sysdate);

I do get the same error "invalid number". In this case the date format of the database should be use. Why is this also not possible ..? I am sure it must have something to do using the "round" function in the "where" clause.

Any hints ..?!

Oliver

Kenneth, Koenraadt wrote:

> Hi Oliver,
>
> '2002-08-19' is a string expression. When evaluating
> round('2002-08-19'), Oracle implicitly tries to convert '2002-02-02'
> to a number, which will not succeed, as you have just experienced.
>
> Instead, use i.e. round(to_date('2002-08-19')).
>
> BTW, it pays off to know the rules of implicit datatype conversion in
> Oracle (can be found in the SQL Reference).
>
> - Kenneth Koenraadt
>
>
> On Tue, 20 Aug 2002 11:42:08 +0200, Oliver Otto <ootto_at_genese.de>
> wrote:
>
> >
> >--------------C3FB9ED6159CDE9A72A7EFF8
> >Content-Type: text/plain; charset=us-ascii
> >Content-Transfer-Encoding: 7bit
> >
> >Hi,
> >
> >maybe someone can explain me this behavior of Oracle. I do not
> >understand the documentation in this.
> >
> >Doing this :
> >
> > select benutzer, round(anlagedatum) from zeit_table;
> >
> >selects a a date from a datetime-column and the names of my users.
> >
> >Ding this:
> >
> > select benutzer, round(anlagedatum) from zeit_table where
> >round(sysdate)=round('2002-08-19');
> >
> >result in an error ORA-001722: invalidate number. I do not understand
> >this. Reading the Documentation I understand ROUND returning a date
> >value. Is it not possible to compare the return values ..?!
> >
> >Maybe someone can give me a hind. Thanx a lot.
> >
> >Oliver
> >
> >
> >
> >
> >
> >--------------C3FB9ED6159CDE9A72A7EFF8
> >Content-Type: text/html; charset=us-ascii
> >Content-Transfer-Encoding: 7bit
> >
> ><!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> ><html>
> >Hi,
> ><p>maybe someone can explain me this behavior of Oracle. I do not understand
> >the documentation in this.
> ><p>Doing this :
> ><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select benutzer, round(anlagedatum)&nbsp;
> >from zeit_table;
> ><p>selects a a date from a datetime-column and the names of my users.
> ><p>Ding this:
> ><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select benutzer, round(anlagedatum)&nbsp;
> >from zeit_table where round(sysdate)=round('2002-08-19');
> ><p>result in an error ORA-001722: invalidate number. I do not understand
> >this. Reading the <a href="http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/functions104a.htm#78667">Documentation</a>
> >I understand ROUND returning a date value. Is it not possible to compare
> >the return values ..?!
> ><p>Maybe someone can give me a hind. Thanx a lot.
> ><p>Oliver
> ><br>&nbsp;
> ><br>&nbsp;
> ><br>&nbsp;
> ><br>&nbsp;</html>
> >
> >--------------C3FB9ED6159CDE9A72A7EFF8--
> >
Received on Tue Aug 20 2002 - 05:53:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US