怎么比较时间

[yuga]

TimeCardNo MemberId MorningTime OffTime
223 1 2002-8-3 8:30:22 2002-8-3 18:30:22
......

我想知道，一段时间每个人早于8：00上班的的和晚于19：00下班的次数
时间是datetime
总是有问题

[hjb111]

select *,count(*) 考勤 from yourtable where convert(varchar,108,Morningtime)<'08:00:00' and convert(varchar,108,OffTime)>'19:00:00' and convert(varchar,102,MorningTime)=convert(varchar,108,OffTime)

[goneaway]

--早于8：00上班
select count(*) from table
where substring(convert(nvarchar(20),MorningTime,120),12,8)<'08:00:00'
--晚于19：00下班
select count(*) from table
where substring(convert(nvarchar(20),MorningTime,120),12,8)>'19:00:00'

[goneaway]

笔误，更正
--早于8：00上班
select count(*) from table
where substring(convert(nvarchar(20),MorningTime,120),12,8)<'08:00:00'
--晚于19：00下班
select count(*) from table
where substring(convert(nvarchar(20),OffTime,120),12,8)>'19:00:00'

[z5wjz]

select memberid,count(*)
from yourtable
where datepart(hh,morningtime)<8 and datepart(hh,offtime)>19 and datetime=你的日期
group by memberid

[CrazyFor]

select memberid
,sum(case when right(convert(char(16),morningtime,120),5)<'08:00' then 1 else 0 end ) [早于8：00上班]
,sum(case when right(convert(char(16),offtime,120),5)>'19:00' then 1 else 0 end ) [晚于19：00下班]
from 表 group by memberid