if
( /* 4,对比员工的打卡是否在异常*/
case when DATEDIFF(ss,case when @yymmdd1<@yymmdd then dateadd(hh,-24,@cardtime1) else @cardtime1 end,@cardtime)>( select top 1 IntervalTime from EMRealMonitorTimeSet where mid1=@mid1 and mid2=@mid)*60
and @empid=@empid1 and exists( select top 1 mid1,mid2 from IntervalTime where mid1=@mid1 and mid2=@mid and @cardtime between time1 and time2 and @cardtime1 between time1 and time2)
or (select top 1 IsnoEntry from EMRealMonitorTimeSet where mid1=@mid1 and mid2=@mid)=1
then 1 else 0
end
)>0
( /* 4,对比员工的打卡是否在异常*/
case when DATEDIFF(ss,case when @yymmdd1<@yymmdd then dateadd(hh,-24,@cardtime1) else @cardtime1 end,@cardtime)>( select top 1 IntervalTime from EMRealMonitorTimeSet where mid1=@mid1 and mid2=@mid)*60
and @empid=@empid1 and exists( select top 1 mid1,mid2 from IntervalTime where mid1=@mid1 and mid2=@mid and @cardtime between time1 and time2 and @cardtime1 between time1 and time2)
or (select top 1 IsnoEntry from EMRealMonitorTimeSet where mid1=@mid1 and mid2=@mid)=1
then 1 else 0
end
)>0
dateadd:ORACLE中ADDMONTH函数
--oracle中不支持sql server中的datediff函数,
--sql server中datediff函数是用于回去两个日期值之间的年月日时分秒:
--oracle中求两个日期之间的天数,小时,分钟,秒,有以下方法:
with t as(
select to_date('2011-04-03 07:10:55','yyyy-mm-dd hh24:mi:ss') dt1
,to_date('2011-05-18 06:25:55','yyyy-mm-dd hh24:mi:ss') dt2
from dual)
select numtoyminterval(months_between(dt2,dt1),'month') y_m
,numtodsinterval(dt2-add_months(dt1,trunc(months_between(dt2,dt1))),'day') d_s
from t
/
Y_M D_S
--------------------------------------- ---------------------------------------
+000000000-01 +000000014 23:15:00.000000000
--
with t as(
select to_timestamp('2011-04-03 07:10:55','yyyy-mm-dd hh24:mi:ss') dt1
,to_timestamp('2011-05-18 06:25:55','yyyy-mm-dd hh24:mi:ss') dt2
from dual)
select dt2-dt1
from t
/
DT2-DT1
---------------------------------------
+000000044 23:15:00.000000000
--
with t as(
select to_timestamp('2011-04-03 07:10:55','yyyy-mm-dd hh24:mi:ss') dt1
,to_timestamp('2011-05-18 06:25:55','yyyy-mm-dd hh24:mi:ss') dt2
from dual)
select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
,extract(second from dt2-dt1) second
from t
/
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
44 23 15 0
sql server datediff函数
oracle extract()函数
oracle 不支持top子句,通过rownum实现分页
oracle 通过rownum实现分页