.net执行oracle查询语句报错“指定的转换无效”解决方案

问题：

　　.net执行oracle查询语句报错“指定的转换无效”，在PL/SQL中正常；

SELECT A.ID,SUM(TO_NUMBER(A.MODIFYTIME-A.UPLOADTIME)*24*60) AS NSUM FROM STUDIO A GROUP BY A.ID

原因：

　　查询时计算两个时间间隔的小数位数太长导致.net无法处理，需要在计算时截取小数位；

解决方法：

　　使用‘ROUND’截取小数位；

SELECT A.ID,SUM(TO_NUMBER(ROUND(A.MODIFYTIME-A.UPLOADTIME,5))*24*60) AS NSUM FROM STUDIO A GROUP BY A.ID

 

扩展：

　　oracle时间间隔处理语句：

SELECT TO_CHAR(EXTRACT(DAY FROM NUMTODSINTERVAL(MODIFYTIME - UPLOADTIME,'DAY'))) || '日',
       TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(MODIFYTIME - UPLOADTIME,'DAY'))) || '时',
       TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(MODIFYTIME - UPLOADTIME,'DAY'))) || '分',
       TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(MODIFYTIME - UPLOADTIME,'DAY'))) || '秒'
FROM STUDIO