I have the following table
Alarm (AlarmID INT, InstalledDate Date)
Given that the alarm need to be replace every 5 years, how do i display all the alarms that is due for replacement in the next 6 months?
I tried the following and there was no result:
SELECT AlarmID
FROM Alarm
WHERE Add_months(InstalledDate, 60)
BETWEEN SYSDATE AND Add_months(SYSDATE, 6);
"I tried the following and there was no result:"
The query you propose looks correct, so perhaps you don't have any ALARMS which are five years old?
"it seems like there is a difference using BETWEEN SYSDATE AND Add_months(SYSDATE, 6) compare to BETWEEN Add_months(SYSDATE, 6) AND SYSDATE;"
The BETWEEN operator demands that we pass the two values in a specific order, lower bound then upper bound. So this filter is true:
where date '2012-03-01' between date '2012-01-01' and date '2012-06-01'
whereas this is false:
where date '2012-03-01' between date '2012-06-01' and date '2012-01-01'
Perhaps this seems unfair, but the Oracle documentation makes it clearer by translating the BETWEEN operator into lt and gt statements:
where date '2012-03-01' >= date '2012-01-01'
and date '2012-03-01' <= date '2012-06-01'
If you swap the values of the second and third expressions you'll see why the reversed order returns false.
