oracle中没有datediff()函数

在oracle中没有datediff()函数
可以用以下方法在oracle中实现该函数的功能:
1.利用日期间的加减运算
天：
ROUND(TO_NUMBER(END_DATE - START_DATE))
小时：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
分钟：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒： ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60) 2.写函数 Create Or Replace Function CDate(Datechar In Varchar2) Return Date Is ReallyDo Date; Begin Select to_date(to_char(to_date(to_char(Datechar),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD'),'YYYY-MM-DD') Into ReallyDo From Dual; Return(ReallyDo); End CDate; Create Or Replace Function CDateTime(Datechar In Varchar2) Return Date Is ReallyDo Date; Begin Select to_date(to_char(to_date(to_char(Datechar),'YYYY-MM-DD HH24:MI:SS') Into ReallyDo From Dual; Return(ReallyDo); End CDateTime; Create Or Replace Function Datediff ( Datepart In Varchar2,StartDate In Varchar2,EndDate In Varchar2 ) Return Number Is ReallyDo Numeric; Begin Select Case Upper(Datepart) When 'YYYY' Then Trunc(Extract(Year From CDate(EndDate)) - Extract(Year From CDate(StartDate))) When 'M' Then Datediff('YYYY',StartDate,EndDate) * 12 + (Extract(Month From CDate(EndDate)) - Extract(Month From CDate(StartDate))) When 'D' Then Trunc(CDate(EndDate) - CDate(StartDate)) When 'H' Then Datediff('D',EndDate) * 24 + (to_Number(to_char(CDateTime(EndDate),'HH24')) - to_Number(to_char(CDateTime(StartDate),'HH24'))) When 'N' Then Datediff('D',EndDate) * 24 * 60 + (to_Number(to_char(CDateTime(EndDate),'MI')) - to_Number(to_char(CDateTime(StartDate),'MI'))) When 'S' Then Datediff('D',EndDate) * 24 * 60 * 60 + (to_Number(to_char(CDateTime(EndDate),'SS')) - to_Number(to_char(CDateTime(StartDate),'SS'))) Else -29252888 End Into ReallyDo From Dual; Return(ReallyDo); End Datediff;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!