CODEWITHSUNDEEP
sqloracle-databaseselect
Z.V
Z.V

Reputation: 1471

SELECT Product rows with expired date

i have a table as follows:

PRODUCT(P_CODE, DESCRIPTION, PRODUCTION_DATE)

the expired product are those have been produced more than 1 year. How do I list all the products that have already expired & together with their expiration date ?

Upvotes: 2

Views: 8482

Answers (4)

Gaurav Soni
Gaurav Soni

Reputation: 6346

create table PRODUCT(P_CODE number, DESCRIPTION varchar2(200), PRODUCTION_DATE date);

insert into product values(1,'XXX',to_date('12-03-2013','dd-mm-yyyy'));

insert into product values(2,'YYY',to_date('13-03-2012','dd-mm-yyyy'));

insert into product values(3,'ZZZ',to_date('12-08-2012','dd-mm-yyyy'));

insert into product values(4,'AAA',to_date('16-08-2013','dd-mm-yyyy'));   

select p_code
      ,description
      ,production_date
      ,add_months(production_date,12) expire_date  
 from product
where production_date<add_months(sysdate,-12)

output

Upvotes: 5

user2687154
user2687154

Reputation: 1

SELECT * FROM PRODUCT WHERE DATEDIFF(YEAR,CURDATE(),DATE_ADD(PRODUCTION_DATE, INTERVAL 1 YEAR)) > 1

That's a wag at it but THIS LINK should get you where you want to go

Upvotes: 0

galets
galets

Reputation: 18492

I don't know how it's done in oracle, but in MS SQL it is:

SELECT P_CODE, DESCRIPTION, PRODUCTION_DATE
FROM PRODUCT
WHERE PRODUCTION_DATE < DATEADD(yy, -1, GETUTCDATE())

Upvotes: 0

Pasha Riger
Pasha Riger

Reputation: 314

SELECT  P_CODE,PRODUCTION_DATE
FROM    PRODUCT
WHERE   PRODUCTION_DATE >= NOW() - INTERVAL 12 MONTH

Upvotes: 4

Related Questions