Re: Date functions

From: Richard Foote <Richard.Foote_at_bigpond.com>
Date: Tue, 20 Aug 2002 20:53:49 +1000
Message-ID: <3D621FBD.B6E0ABAC@bigpond.com>

Hi Oliver,

You got me this time.

The where clause is rounding two valid dates and should work fine (if having a condition that is always true is fine).

Are you sure the error message is not referring to round(anlagedatum), whatever an anlagedatum is (sounds like a large naughty Australian slang word but I won't go there).

Cheers

Richard

Oliver Otto wrote:
>
> 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,
> > >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 <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 ..?!
> > >Maybe someone can give me a hind. Thanx a lot.
> > >Oliver
> > >  
> > >  
> > >  
> > >  </html>
> > >
> > >--------------C3FB9ED6159CDE9A72A7EFF8--
> > >
Received on Tue Aug 20 2002 - 05:53:49 CDT