CODEWITHSUNDEEP
oracle-databaseplsqldate-arithmetic
FirstTimer
FirstTimer

Reputation: 45

using date function to query

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);

Upvotes: 0

Views: 171

Answers (1)

APC
APC

Reputation: 146239

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

Upvotes: 1

Related Questions