select id,name, case when datepart(mi,date)<9 then '否',when datepart(mi,date)=9 and datepart(hh,date) < 30 then '否',else '是' end [是否迟到] from TableName where datediff(d,date,getdate())=0--通过改变这里变换查询时间这个是查询今天出勤状况的 没有测试 觉得可以 如果不对的话 就当是一个思路吧
DECLARE @NowTime datetime; SET @NowTime = (SELECT CONVERT(VARCHAR(10),GETDATE(),120)+'09:30:00'); SELECT Id,name from 表 where DATEDIFF(SECOND,[date],@NowTime)<0
where convert (varchar(10),date,108)>'09:30:00'
如果存在打多次卡呢select id,name, case when datepart(hh,date)<9 then '否',when datepart(hh,date)=9 and datepart(Minute,date) < 30 then '否',else '是' end from TableName t1 where datediff(day,date,getdate())=0--通过改变这里变换查询时间 and exists ( select*from TableName t2 where t2.name=t1.name and t2.date>t1.date and datediff(day,t2.date,getdate())=0
)
4楼的,应该是and not exists吧
select *,case when convert(varchar(8),getdate(),108) >'09:30:00' then '迟到' else '正常' end [是否迟到] from tb
select name,Convert(varchar(10),date,120) as 日期,case when convert(varchar(8),min(date),108) >'09:30:00' then '迟到' else '正常' end [是否迟到] from tb group by name, Convert(varchar(10),date,120)
SET @NowTime = (SELECT CONVERT(VARCHAR(10),GETDATE(),120)+'09:30:00');
SELECT Id,name from 表 where DATEDIFF(SECOND,[date],@NowTime)<0
case when datepart(hh,date)<9 then '否',when datepart(hh,date)=9 and datepart(Minute,date) < 30 then '否',else '是' end
from TableName t1
where datediff(day,date,getdate())=0--通过改变这里变换查询时间
and exists
( select*from TableName t2 where t2.name=t1.name
and t2.date>t1.date
and datediff(day,t2.date,getdate())=0
)
select *,case when convert(varchar(8),getdate(),108) >'09:30:00' then '迟到' else '正常' end [是否迟到]
from tb
from tb group by name, Convert(varchar(10),date,120)