CODEWITHSUNDEEP
oracle-databasedate
user1816507
user1816507

Reputation: 89

oracle - how to list out the products that are going to expire in 2months time?

assume all the product expires in 1years

how to list out the products that are going to expire in 2months time?

table product
--------------------------------

food    | ManufacturedDate| 
--------------------------------
apple   | 12-nov-11  
orange  | 12-jan-12  
pear    | 12-jan-12  
milk    | 12-nov-11
mango   | 12-nov-11

output

orange  | 12-jan-12
pear    | 12-jan-12

Upvotes: 0

Views: 136

Answers (2)

Bruno
Bruno

Reputation: 5822

Try: 

select * 
from product
where add_months( trunc(ManufacturedDate), 12 ) >= trunc(SYSDATE)
and add_months( trunc(ManufacturedDate), 12) <= add_months(trunc(SYSDATE), 2);

Upvotes: 0

DazzaL
DazzaL

Reputation: 21993

you data says that the others apart from pear+orange expire today, so assuming you want to exclude expiring today and include those expiring WITHIN 2 months time:

SQL> select food, manufacturedate, add_months(manufacturedate,12) expiry_date from product where add_months(manufacturedate, 12) <= add_months(trunc(sysdate), 2) and add_months(manufacturedate, 12) > trunc(sysdate);

FOOD        MANUFACTU EXPIRY_DA
--------------- --------- ---------
orange      12-JAN-12 12-JAN-13
pear        12-JAN-12 12-JAN-13

or a more index friendly way of putting it (removing the functions on the column side):

SQL> select food, manufacturedate, add_months(manufacturedate,12) expiry_date from product where manufacturedate <= add_months(trunc(sysdate), -10) and manufacturedate > add_months(trunc(sysdate), -12);

FOOD        MANUFACTU EXPIRY_DA
--------------- --------- ---------
orange      12-JAN-12 12-JAN-13
pear        12-JAN-12 12-JAN-13

Upvotes: 1

Related Questions