handling dates in derby within query

Question

I have expiry and manufacturing dates (data type DATE) in products table in database and I want to apply following formula to get my desired result:

SELECT * 
  FROM products 
 WHERE (RecentDate - expiry / manufacturing - expiry) * 100 > = 70;

it will show the products whose life time has exceeded more than 70 percent.

How can I design a query for this task (I am using Derby ).

I tried this but didn't succeed

SELECT * FROM PRODUCTS
 WHERE ({fn TIMESTAMPDIFF(SQL_TSI_DAY, CURRENT_TIMESTAMP, EXPIRY)} / 
        {fn TIMESTAMPDIFF(SQL_TSI_DAY, MDATE, EXPIRY )}) * 100 > 70;

division of these two timestampdiff give 0.I don't know why?

i have tried DATEDIFF() function but it doesn't works for derby — Hasnain, Jan 08 '15 at 12:48
`select {fn timestampdiff(SQL_TSI_FRAC_SECOND,EXPIRY , MDATE)} as diff from PRODUCTS;` this is what i have found something relative...it gives me seconds between two dates.. — Hasnain, Jan 08 '15 at 13:00
i have found a clue here [http://stackoverflow.com/questions/11511805/using-timestampdiff-in-a-derby-where-clause] — Hasnain, Jan 08 '15 at 13:23
this query which i'm using doesn't gives me any result ` SELECT * FROM PRODUCTS WHERE ( ({fn TIMESTAMPDIFF( SQL_TSI_DAY,EXPIRY,CURRENT_TIMESTAMP )}) / ({fn TIMESTAMPDIFF( SQL_TSI_DAY,MDATE,EXPIRY )}))*100 > 70;` — Hasnain, Jan 08 '15 at 15:27

score 0 · Answer 1 · answered Jan 08 '15 at 14:02

0

The the below idea:

select * from  products 
where 
convert(float,DATEDIFF(dd,manufacturing,GETDATE()))/convert(float,DATEDIFF(dd,manufacturing,expiry))*100 > = 70;

answered Jan 08 '15 at 14:02

Mohammed

313
1
6

score 0 · Accepted Answer · answered Jan 08 '15 at 19:25

After hours of playing around i sorted out the query.Thanks to all who helped me through!
Here it is:

SELECT EXPIRY,MDATE FROM PRODUCTS WHERE cast( {fn TIMESTAMPDIFF( SQL_TSI_DAY,MDATE,CURRENT_TIMESTAMP)} as double) /cast( {fn TIMESTAMPDIFF( SQL_TSI_DAY,MDATE,EXPIRY )} as DOUBLE)*100 > 70 ;

Solution was to cast the TIMESTAMPDIFF as DOUBLE.With out casting, it could not calculate the percentage..why, that i also am not sure.But some how it worked for me.Any one with knowledge, please do share.Cheers!

handling dates in derby within query

2 Answers2

Linked