如何列出将在2个月内到期的产品

Question

假定所有产品在1年中

如何列出要在2个月时间到期的产品？

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

输出

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

Answer 1

您的数据说，除梨 橙色以外的其他人今天出现了，因此假设您想排除今天到期并包括在2个月内到期的人：

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

或一种更友好的征用方式（将功能删除在列侧）：

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

Answer 2

尝试：

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