oracle中没有datediff()函数

秒速五厘米 2022-07-11 05:57 480阅读 0赞

在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)  
[毫秒][Link 1]：  
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'),    
                           'YYYY-MM-DD HH24:MI:SS'),    
                   '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', StartDate, EndDate) \* 24 +    
                (to\_Number(to\_char(CDateTime(EndDate), 'HH24')) -    
                 to\_Number(to\_char(CDateTime(StartDate), 'HH24')))    
               When 'N' Then   
                Datediff('D', StartDate, EndDate) \* 24 \* 60 +    
                (to\_Number(to\_char(CDateTime(EndDate), 'MI')) -    
                 to\_Number(to\_char(CDateTime(StartDate), 'MI')))    
               When 'S' Then   
                Datediff('D', StartDate, 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;

[Link 1]: https://www.baidu.com/s?wd=%E6%AF%AB%E7%A7%92&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YLnAmYny7bnWTkP19hmhDk0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPjn4rHfYrjf4