Oracle时间戳间隔天数计算

SELECT

(TO_NUMBER(TO_CHAR(b.PAY_TIME,'yyyymmdd')) - TO_NUMBER(TO_CHAR(b.PRE_TIME,'yyyymmdd')))

  FROM TABLE

WHERE 条件

1、先将对应的时间戳字段按照格式转换为to_char,再将to_char转换为number后计算

 

SELECT TO_NUMBER(TO_CHAR(SYSDATE,'yyyy')) AS YEAR_ID,
       10026 AS KPI_ID,
       bp.MANAGE_DEPART_ID AS DEPT_ID,
       (TO_NUMBER(TO_CHAR(b.PAY_TIME,'yyyymmdd')) - TO_NUMBER(TO_CHAR(b.PRE_TIME,'yyyymmdd'))) / d.KPI_VALUE AS KPI_VALUE,
       '1' AS CREATE_USER_ID,
       SYSDATE AS CREATE_TIME
  FROM table b
  JOIN table bp ON b.PROJECT_ID = bp.PROJECT_ID
  JOIN table d ON bp.MANAGE_DEPART_ID = d.DEPT_ID
 WHERE b.LAST_AUDIT_STATUS = 1
   AND b.APPLY_TIME >= TRUNC(SYSDATE,'yyyy')
   AND b.APPLY_TIME < = ADD_MONTHS(TRUNC(SYSDATE,'yyyy'),12) --预受理时间
   AND d.KPI_ID = 10024;
 

date类型计算天数
将字符串转日期
select to_date('06/06/2020','mm/dd/yyyy')-to_date('06/01/2020','mm/dd/yyyy') from dual;

date类型当前时间与某一date类型字段天数差

select trunc(sysdate) - trunc(create_time) from table;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值