I want to do the following comparison :
IF ( V_C_CRITERE2.DATE_FIN_SOUHAITEE - TO_DATE(V_SEUIL_ALERT) < CURRENT_DATE) THEN
DATE_FIN_SOUHAITEE is a DATE.
But a get this error :
operator does not exist: interval < date
How can i do the comparison?
Best Answer
Although the question states that
DATE_FIN_SOUHAITEE
is a DATE, the error message "operator does not exist: timestamp without time zone - numeric" makes it clear that it really is atimestamp
. In Postgres - unlike Oracle - atimestamp
is something substantially different than adate
(in Oracle they are more or less the same thing - they just differ in the precision).In Postgres you can only subtract an
interval
from atimestamp
, and you can subtract integer values from adate
If V_SEUIL_ALERT is indeed a varchar that stores a number that represents years, you need to convert that string value to a proper interval in order to be able to subtract it from a
timestamp
:the above can then be compared using
<
withcurrent_date
.I don't know what the original code in Oracle did, because
DATE_FIN_SOUHAITEE - V_SEUIL_ALERT
would implicitly convert V_SEUIL_ALERT to anumber
which is then taken as days to be subtracted from DATE_FIN_SOUHAITEE, not years.If that column indeed stores days rather than years (as stated in the comments), you need to use:
You should really take the opportunity of that migration and fix the wrong data type for the column
V_SEUIL_ALERT
to be an integer, numeric or eveninterval
if that is what you use it for. Never store numbers in VARCHAR columns. That was already a really, really bad idea in Oracle.