请教各位高手: 我需要一个自定义函数,功能和sqlserver中的完全一样的datediff函数, 虽然网上面有很多,但是在执行的时候多是这样的:datediff('d',time,sysdate)
time是列名,sysdate是oracle取得当前系统时间。 但是在sqlserver中是这样执行的 第一种: datediff(d,time,getdate()) 第二种:datediff("d",time,getdate()) 在oracle执行的时候d多了两个单引号我现在要做的是兼容sqlserver和oracle数据库,我已经在oracle中自定义了一个getdate()的方法就后面的单引号无法统一,想了很多的办法就是无法达成一致, 请高手指点下 感激不尽!
下面是我的函数:
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 'DAY' Then
CDate(EndDate) - CDate(StartDate)
When 'HH' Then
Datediff('DAY', StartDate, EndDate) * 24 +
(to_Number(to_char(CDateTime(EndDate), 'HH24')) -
to_Number(to_char(CDateTime(StartDate), 'HH24')))
When 'N' Then
Datediff('DAY', StartDate, EndDate) * 24 * 60 +
(to_Number(to_char(CDateTime(EndDate), 'MI')) -
to_Number(to_char(CDateTime(StartDate), 'MI')))
When 'S' Then
Datediff('DAY', 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
time是列名,sysdate是oracle取得当前系统时间。 但是在sqlserver中是这样执行的 第一种: datediff(d,time,getdate()) 第二种:datediff("d",time,getdate()) 在oracle执行的时候d多了两个单引号我现在要做的是兼容sqlserver和oracle数据库,我已经在oracle中自定义了一个getdate()的方法就后面的单引号无法统一,想了很多的办法就是无法达成一致, 请高手指点下 感激不尽!
下面是我的函数:
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 'DAY' Then
CDate(EndDate) - CDate(StartDate)
When 'HH' Then
Datediff('DAY', StartDate, EndDate) * 24 +
(to_Number(to_char(CDateTime(EndDate), 'HH24')) -
to_Number(to_char(CDateTime(StartDate), 'HH24')))
When 'N' Then
Datediff('DAY', StartDate, EndDate) * 24 * 60 +
(to_Number(to_char(CDateTime(EndDate), 'MI')) -
to_Number(to_char(CDateTime(StartDate), 'MI')))
When 'S' Then
Datediff('DAY', 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
Create Or Replace Function Datediff
(
Datepart In Varchar2,
StartDate In Varchar2,
EndDate In Varchar2
)Return Number其中
Datepart是一个varchar2类型的参数所以调用的时候,当然是要加上''的,这表示只一个字符串呀。
但是,通用的结果会带来查询效率的降低,要做好准备。
2.接下来,需要在mysql和mssql里各添加一个函数,实现部分(通过判断参数调用内部datediff)。