Re: Date functions

From: <Kenneth>
Date: Tue, 20 Aug 2002 11:03:06 GMT
Message-ID: <3d621f35.13584533@news.capgemini.se>

On Tue, 20 Aug 2002 12:53:43 +0200, Oliver Otto <ootto_at_genese.de> 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
>

Hi Oliver,

select '1' from dual where round(sysdate)=round(sysdate)

returns no error.

So it has nothing to do with the where clause. I guess the reason is that you 'anlagedatum' column is not a date field, but a char field. That would explain it all.

As I wrote in the previous post, ROUND('some-char') will always try to convert 'some-char' to a NUMBER, NOT a DATE, regardless of the format of 'some-char'.

I wrote in the previous post that it is good to know the implicit conversion rules of Oracle. That is true, but only because it will help you debug applications, in which the programmer either used or confused the implicit conversions.

You should always use EXPLICIT conversion, which is more readable and less likely to cause errors.

Kenneth Koenraadt

>
>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 - 06:03:06 CDT