在查询中处理德比中的日期 [英] handling dates in derby within query

问题描述

我在数据库的产品表中有有效期和生产日期（数据类型DATE），我想应用以下公式来获得所需的结果：

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;

它将显示使用寿命超过70％的产品。

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

如何设计此任务的查询（我正在使用Derby。）

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;

这两个timestampdiff的除法为0。我不知道为什么？

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

推荐答案

经过数小时的游戏，我对查询进行了整理。感谢所有帮助我完成任务的人！ b $ b

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

从已转换的产品中选择过期的MDATE（{fn TIMESTAMPDIFF（SQL_TSI_DAY，MDATE，CURRENT_TIMESTAMP）}为double）/ cast（{fn TIMESTAMPDIFF（SQL_TSI_DAY，MDATE，EXPIRY}} * 100> 70;

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 ;

解决方案是将TIMESTAMPDIFF强制转换为DOUBLE。如果不进行强制转换，则无法计算百分比..为什么，我也不确定。但是有些方法对我有用。任何有知识的人都请分享。欢呼！

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!

这篇关于在查询中处理德比中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！