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?
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!