oracle中没有datediff()函数

最新推荐文章于 2023-06-30 17:05:44 发布

转载最新推荐文章于 2023-06-30 17:05:44 发布 · 1.4k 阅读

数据库同时被 2 个专栏收录

334 篇文章

订阅专栏

Oracle

65 篇文章

订阅专栏

本文介绍了在Oracle数据库中如何实现DATEDIFF函数的功能，通过日期之间的加减运算和创建自定义函数两种方式，实现了年、月、日、小时、分钟、秒等不同粒度的时间间隔计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在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'),
                           '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;