Re: Date functions

From: <Kenneth>
Date: Tue, 20 Aug 2002 09:54:52 GMT
Message-ID: <3d620f71.9548329@news.capgemini.se>

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 - 04:54:52 CDT