monthdiff oracle_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'),

'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;

Oracle常用函数:DateDiff() 返回两个日期之间的时间间隔自定义函数

(当然也有其他方法实现，在ORACLE里日期类型是可以直接进行比较的，最后会给出方法示例)

Sql代码  

CreateOrReplaceFunctionCDate(DatecharInVarchar2)ReturnDateIs

ReallyDo Date;

Begin

Selectto_date(to_char(to_date(to_char(Datechar),'YYYY-MM-DD HH24:MI:SS'),

'YYYY-MM-DD'),

'YYYY-MM-DD')

IntoReallyDo

FromDual;

Return(ReallyDo);

EndCDate;

CreateOrReplaceFunctionCDateTime(DatecharInVarchar2)ReturnDateIs

ReallyDo Date;

Begin

Selectto_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')

IntoReallyDo

FromDual;

Return(ReallyDo);

EndCDateTime;

CreateOrReplaceFunctionDatediff

(

Datepart  InVarchar2,

StartDate InVarchar2,

EndDate   InVarchar2

) ReturnNumberIs

ReallyDo Numeric;

Begin

SelectCaseUpper(Datepart)

When'YYYY'Then

Trunc(Extract(YearFromCDate(EndDate)) -

Extract(YearFromCDate(StartDate)))

When'M'Then

Datediff('YYYY', StartDate, EndDate) * 12 +

(Extract(MonthFromCDate(EndDate)) -

Extract(MonthFromCDate(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

IntoReallyDo

FromDual;

Return(ReallyDo);

EndDatediff;

当然也有其他方法实现

在ORACLE里日期类型是可以直接进行比较的。举个例子：

SQL> SELECT to_date('2011-10-05', 'yyyy-mm-dd') - to_date('2011-10-07', 'yyyy-mm-dd') FROM dual ;

TO_DATE('2011-10-05','YYYY-MM-DD')-TO_DATE('2011-10-07','YYYY-MM-DD')

---------------------------------------------------------------------

-2

这说明2011-10-05要比7号早2天。

SQL> SELECT to_date('2011-10-07 14:23:24' , 'yyyy-mm-dd hh24:mi:ss') + 1/24 FROM dual ;

TO_DATE('2011-10-07

-------------------

2011-10-07 15:23:24

SQL>

这是说明在某一时间上加一小时，1是代表一天，1/24就是一小时，同理1/24/60就是一分钟