Re: comparing a DATE column with "one minute ago", type warning?

From: joel garry <joel-garry_at_home.com>
Date: Fri, 10 Oct 2008 15:50:48 -0700 (PDT)
Message-ID: <4d91256d-9145-43dc-97db-5687864eb164@f37g2000pri.googlegroups.com>


On Oct 9, 3:14 am, Laurenz Albe <inv..._at_spam.to.invalid> wrote:
> m..._at_pixar.com wrote:
> > How should I be comparing a DATE column with "one minute ago"?
>
> > I do this:
> >    select sysdate, sysdate- interval '1' minute from dual;
>
> > and get just what I need, the time a minute ago:
>
> >    SYSDATE                   SYSDATE-INTERVAL'1'MINUTE
> >    ------------------------- -------------------------
> >    08-OCT-08 17:09:19        08-OCT-08 17:08:19        
>
> > But in my PL/SQL, I get a warning on this code, where
> > lastping is a DATE column:
>
> >        update mytable
> >           set status='silent'
> >         where lastping < (sysdate - interval '1' minute);
> >                           ^       ^
> >                      col 28     col 36
>
> > Warning(16,28): PLW-07202: bind type would result in conversion
> >                away from column type
> > Warning(16,36): PLW-07202: bind type would result in conversion
> >                away from column type
>
> I'd say that the warning is bogus and should be ignored.
>
> According tohttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_el...
> the difference between DATE and INTERVAL is a DATE, so if lastping is
> a DATE there should be no implicit conversion necessary.
>
> Yours,
> Laurenz Albe

I'd say you are incorrectly assuming that since the difference is a DATE and one of the operands is a DATE, there is no conversion. But if you scroll up a bit from where your link points, you see "When you pass a timestamp, interval, or numeric value to a built-in function that was designed only for the DATE datatype, Oracle implicitly converts the non-DATE value to a DATE value..." I'd agree that it pointing to sysdate as part of the problem is kind of strange, but code rules over docs, especially when coding.

jg

--
@home.com is bogus.  "The Fox News story is wrong and is riddled with
falsehoods and errors. The story cites misinformation from
unattributed sources and leaked emails that are taken out of
context..." http://www.foxnews.com/story/0,2933,435681,00.html
Received on Fri Oct 10 2008 - 17:50:48 CDT

Original text of this message