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

joel garry <joel-garry_at_home.com> wrote:

>> >         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.

Hmm, it would be nice if you could explain that in more detail, because to me it still apears like this:

• The operator "-" is not "designed only for the DATE datatype", so the quotation should not apply in this case, right?
• There should be no conversion necessary because, in this case, the function "-" is used on a DATE and an INTERVAL DAY TO SECOND argument, and the DATE that (according to the documentation) is supposed to result is compared to a DATE column.

Where do I go wrong?

> 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.

Those are wise words.

Two things feed my doubt here:
- I have frequently encountered wrong warnings from the PL/SQL   compiler, for example about "unreachable code" that was happily reached   later.
- As has been remarked, there is no warning for "sysdate - 1/1440", yet   according to the documentation:
    "If one operand is a DATE value or a numeric value ...      ... Oracle implicitly converts the other operand to DATE data."   So there should be a warning in this case, no?

Yours,
Laurenz Albe Received on Mon Oct 13 2008 - 06:09:19 CDT