Re: Date functions

From: Oliver Otto <ootto_at_genese.de>
Date: Tue, 20 Aug 2002 13:46:02 +0200
Message-ID: <3D622BFA.5BD8F743@genese.de>

Hi,

aeh thanx a lot for all your help. I forgot the main thing we change two weeks ago. You all are right the problem was the date colum which we change towards a char column.

Oh oh, sorry for asking such a stupid question I should have seen it before by myself.

Oliver

Kenneth, Koenraadt wrote:

> 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:46:02 CDT