高手进！如何把Sql里面的datediff()方法，在Oracle里面如何替换使用啊？

发布网友 发布时间：2022-04-10 01:05

我来回答

共2个回答

热心网友 时间：2022-04-10 02:34

create or replace function datediff(type in varchar2, startTime in varchar2,endTime in varchar2,v in varchar2) return number as
v_result number;
sql_1 varchar2(200);
begin
dbms_output.put_line('1');
if(type = 'day') then
sql_1 := 'select '||v||'(TO_NUMBER(to_date('''||startTime||''',''YYYY-MM-DD'') - to_date('''||endTime||''',''YYYY-MM-DD''))) from al';
dbms_output.put_line('1');
elsif(type = 'month') then
sql_1 := 'select '||v||'(months_between(to_date('''||startTime||''',''YYYY-MM-DD'') - to_date('''||endTime||''',''YYYY-MM-DD''))) from al' ;
dbms_output.put_line('1');
elsif(type = 'week') then
dbms_output.put_line('1');
sql_1 := 'select '||v||'(TO_NUMBER(to_date('''||startTime||''',''YYYY-MM-DD'') - to_date('''||endTime||''',''YYYY-MM-DD''))/7) from al ';
end if;
dbms_output.put_line(sql_1);
EXECUTE IMMEDIATE sql_1 into v_result ;
return (v_result);
end;
--先执行以上函数，这样就在Oracle自己写了一个该函数；
使用时候可以这样：
select datediff('week','2012-09-01','2012-08-01','round') from al ;
--说明'week'表示周,'month'表示月,'day'表示天
--中间传俩日期
--第四个参数：周或月四舍五入用‘round’ ，向上取整用‘ceil’ 向下取整用‘floor’

热心网友 时间：2022-04-10 03:52

首先在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;