oracle中求两日期之间间隔的天数用什么函数?

Question

我原先在SQL SERVER 里用datediff(d,date1,date2)来求两日期之间间隔的天数,但在ORACLE中用什么函数,我在网上找过了,没找到,请问有哪位大侠知道告诉我下?

Answer 1

trunc(date1,'DD') - trunc(date2,'DD')

Answer 2

我在SQL中语句:
CREATE VIEW dbo.V_PURCHASE
AS
SELECT dbo.TB_BUY_PLAN.vcPlanCode, dbo.TB_BUY_PLAN.vcGoodsCode, 
      dbo.TB_BUY_PLAN.vcGoodsName, dbo.TB_BUY_PLAN.vcStreamCode, 
      dbo.TB_BUY_PLAN_ITEM.vcGoodsName AS vcProductName, 
      dbo.TB_BUY_PLAN_ITEM.vcGoodsSize, dbo.TB_BUY_PLAN_ITEM.dPlanBuyDate, 
      dbo.TB_BUY_PLAN_ITEM.dPostDate, 
      dbo.TB_BUY_PLAN_ITEM.fCurentNumber AS fNumber, 
      dbo.TB_BUY_INCOMEBILL_ITEM.fInNumber, 
      dbo.TB_BUY_ORDER_ITEM.vcBuyOrderID, 
      dbo.TB_ORG_PERSON.vcPersonName AS vcPlaner, 
      TB_ORG_PERSON_1.vcPersonName, DATEDIFF(d, 
      dbo.TB_BUY_PLAN_ITEM.dPostDate, dbo.TB_BUY_INCOMEBILL.dAuditDate) 
      AS [date], dbo.TB_BUY_INCOMEBILL.vcStatus, 
      dbo.TB_BUY_INCOMEBILL.dPrePostDate, dbo.TB_BUY_INCOMEBILL.vcAspID, 
      dbo.TB_BUY_INCOMEBILL.vcBillType
FROM dbo.TB_BUY_INCOMEBILL_ITEM INNER JOIN
      dbo.TB_BUY_ORDER_ITEM INNER JOIN
      dbo.TB_ORG_PERSON INNER JOIN
      dbo.TB_BUY_INCOMEBILL INNER JOIN
      dbo.TB_ORG_PERSON TB_ORG_PERSON_1 ON 
      dbo.TB_BUY_INCOMEBILL.vcAspID = TB_ORG_PERSON_1.vcAspID ON 
      dbo.TB_ORG_PERSON.vcAspID = dbo.TB_BUY_INCOMEBILL.vcAspID INNER JOIN
      dbo.TB_BUY_ORDER ON 
      TB_ORG_PERSON_1.vcPersonCode = dbo.TB_BUY_ORDER.vcBuyMaster AND 
      TB_ORG_PERSON_1.vcAspID = dbo.TB_BUY_ORDER.vcAspID AND 
      dbo.TB_BUY_INCOMEBILL.vcAspID = dbo.TB_BUY_ORDER.vcAspID AND 
      dbo.TB_BUY_INCOMEBILL.vcOrderID = dbo.TB_BUY_ORDER.vcBuyOrderID ON 
      dbo.TB_BUY_ORDER_ITEM.vcAspID = dbo.TB_BUY_INCOMEBILL.vcAspID AND 
      dbo.TB_BUY_ORDER_ITEM.vcBuyOrderID = dbo.TB_BUY_ORDER.vcBuyOrderID AND
       dbo.TB_BUY_ORDER_ITEM.vcAspID = dbo.TB_BUY_ORDER.vcAspID INNER JOIN
      dbo.TB_BUY_PLAN_ITEM INNER JOIN
      dbo.TB_BUY_PLAN ON 
      dbo.TB_BUY_PLAN_ITEM.vcPlanID = dbo.TB_BUY_PLAN.vcPlanID AND 
      dbo.TB_BUY_PLAN_ITEM.vcAspID = dbo.TB_BUY_PLAN.vcAspID ON 
      dbo.TB_BUY_ORDER.vcComefrom = dbo.TB_BUY_PLAN.vcPlanID AND 
      dbo.TB_ORG_PERSON.vcAspID = dbo.TB_BUY_PLAN.vcAspID AND 
      dbo.TB_ORG_PERSON.vcPersonCode = dbo.TB_BUY_PLAN.vcPlaner AND 
      dbo.TB_BUY_INCOMEBILL.vcAspID = dbo.TB_BUY_PLAN.vcAspID AND 
      dbo.TB_BUY_ORDER_ITEM.vcGoodsCode = dbo.TB_BUY_PLAN_ITEM.vcGoodsCode AND
       dbo.TB_BUY_INCOMEBILL.vcAspID = dbo.TB_BUY_PLAN_ITEM.vcAspID ON 
      dbo.TB_BUY_INCOMEBILL_ITEM.vcIncomeBillID = dbo.TB_BUY_INCOMEBILL.vcIncomeBillID
       AND 
      dbo.TB_BUY_INCOMEBILL_ITEM.vcAspID = dbo.TB_BUY_INCOMEBILL.vcAspID AND 
      dbo.TB_BUY_INCOMEBILL_ITEM.vcGoodsCode = dbo.TB_BUY_PLAN_ITEM.vcGoodsCode
       AND 
      dbo.TB_BUY_INCOMEBILL_ITEM.vcGoodsCode = dbo.TB_BUY_ORDER_ITEM.vcGoodsCode
我在ORACLE中这样改了:
SELECT TOYERP.TB_BUY_PLAN.vcPlanCode, TOYERP.TB_BUY_PLAN.vcGoodsCode, 
      TOYERP.TB_BUY_PLAN.vcGoodsName, TOYERP.TB_BUY_PLAN.vcStreamCode, 
      TOYERP.TB_BUY_PLAN_ITEM.vcGoodsName AS vcProductName, 
      TOYERP.TB_BUY_PLAN_ITEM.vcGoodsSize, TOYERP.TB_BUY_PLAN_ITEM.dPlanBuyDate, 
      TOYERP.TB_BUY_PLAN_ITEM.dPostDate, 
      TOYERP.TB_BUY_PLAN_ITEM.fCurentNumber AS fNumber, 
      TOYERP.TB_BUY_INCOMEBILL_ITEM.fInNumber, 
      TOYERP.TB_BUY_ORDER_ITEM.vcBuyOrderID, 
      TOYERP.TB_ORG_PERSON.vcPersonName AS vcPlaner, 
      TB_ORG_PERSON_1.vcPersonName,
      DATE:=(TRUNC(TOYERP.TB_BUY_INCOMEBILL.dAuditDate,'DD')-TRUNC(TOYERP.TB_BUY_PLAN_ITEM.dPostDate,'DD'))      
      TOYERP.TB_BUY_INCOMEBILL.vcStatus, 
      TOYERP.TB_BUY_INCOMEBILL.dPrePostDate, TOYERP.TB_BUY_INCOMEBILL.vcAspID, 
      TOYERP.TB_BUY_INCOMEBILL.vcBillType
FROM TOYERP.TB_BUY_INCOMEBILL_ITEM INNER JOIN
      TOYERP.TB_BUY_ORDER_ITEM INNER JOIN
      TOYERP.TB_ORG_PERSON INNER JOIN
      TOYERP.TB_BUY_INCOMEBILL INNER JOIN
      TOYERP.TB_ORG_PERSON TB_ORG_PERSON_1 ON 
      TOYERP.TB_BUY_INCOMEBILL.vcAspID = TB_ORG_PERSON_1.vcAspID ON 
      TOYERP.TB_ORG_PERSON.vcAspID = TOYERP.TB_BUY_INCOMEBILL.vcAspID INNER JOIN
      TOYERP.TB_BUY_ORDER ON 
      TB_ORG_PERSON_1.vcPersonCode = TOYERP.TB_BUY_ORDER.vcBuyMaster AND 
      TB_ORG_PERSON_1.vcAspID = TOYERP.TB_BUY_ORDER.vcAspID AND 
      TOYERP.TB_BUY_INCOMEBILL.vcAspID = TOYERP.TB_BUY_ORDER.vcAspID AND 
      TOYERP.TB_BUY_INCOMEBILL.vcOrderID = TOYERP.TB_BUY_ORDER.vcBuyOrderID ON 
      TOYERP.TB_BUY_ORDER_ITEM.vcAspID = TOYERP.TB_BUY_INCOMEBILL.vcAspID AND 
      TOYERP.TB_BUY_ORDER_ITEM.vcBuyOrderID = TOYERP.TB_BUY_ORDER.vcBuyOrderID AND
       TOYERP.TB_BUY_ORDER_ITEM.vcAspID = TOYERP.TB_BUY_ORDER.vcAspID INNER JOIN
      TOYERP.TB_BUY_PLAN_ITEM INNER JOIN
      TOYERP.TB_BUY_PLAN ON 
      TOYERP.TB_BUY_PLAN_ITEM.vcPlanID = TOYERP.TB_BUY_PLAN.vcPlanID AND 
      TOYERP.TB_BUY_PLAN_ITEM.vcAspID = TOYERP.TB_BUY_PLAN.vcAspID ON 
      TOYERP.TB_BUY_ORDER.vcComefrom = TOYERP.TB_BUY_PLAN.vcPlanID AND 
      TOYERP.TB_ORG_PERSON.vcAspID = TOYERP.TB_BUY_PLAN.vcAspID AND 
      TOYERP.TB_ORG_PERSON.vcPersonCode = TOYERP.TB_BUY_PLAN.vcPlaner AND 
      TOYERP.TB_BUY_INCOMEBILL.vcAspID = TOYERP.TB_BUY_PLAN.vcAspID AND 
      TOYERP.TB_BUY_ORDER_ITEM.vcGoodsCode = TOYERP.TB_BUY_PLAN_ITEM.vcGoodsCode AND
       TOYERP.TB_BUY_INCOMEBILL.vcAspID = TOYERP.TB_BUY_PLAN_ITEM.vcAspID ON 
      TOYERP.TB_BUY_INCOMEBILL_ITEM.vcIncomeBillID = TOYERP.TB_BUY_INCOMEBILL.vcIncomeBillID
       AND 
      TOYERP.TB_BUY_INCOMEBILL_ITEM.vcAspID = TOYERP.TB_BUY_INCOMEBILL.vcAspID AND 
      TOYERP.TB_BUY_INCOMEBILL_ITEM.vcGoodsCode = TOYERP.TB_BUY_PLAN_ITEM.vcGoodsCode
       AND 
      TOYERP.TB_BUY_INCOMEBILL_ITEM.vcGoodsCode = TOYERP.TB_BUY_ORDER_ITEM.vcGoodsCode
但也出现错误,请问怎么解决?

Answer 3

Oracle支持日期计算，你可以创建诸如“日期1－日期2”这样的表达式来计算这两个日期之间的时间差。

Answer 4

select trunc(to_date('2007-3-8','yyyy-mm-dd'),'dd')-trunc(to_date('2007-3-7','yyyy-mm-dd'),'dd')
from dual;