SELECT * FROM TB WHERE TIME >='20110701' AND TIME<'20110801' AND CONVERT(CHAR(5),TIME,108)>'09:40'
where year(date) = 2011 and month(date) = 7 and date between convert(datetime,convert(varchar(11),date,120)+'09:40') and convert(datetime,convert(varchar(11),date,120)+'12:00')
select * from tb where CONVERT(varchar(2),上班时间,110)='07' and convert(varchar(5),上班时间,108)>'09:40'
select * from table1 where datepart(month,table1.date)=7 and convert(varchar(5),table1.date,108)>'09:40'
select * from tb where convert(varchar(5),dt,108) > '09:40' and datediff(mm,'2011-07-01',dt) = 0
select * from table1 where datepart(month,table1.date)=7 and convert(varchar(5),table1.date,108)>'09:40'
select m.Department 部门名称, count(1) 迟到次数 from staff_info m , time_record n where m.Staff_code = n.Staff_code and datepart(mm,arrive_time) = 7 and convert(varchar(5),n.arrive_time,108) > '09:40' group by m.Department having count(1) >= 3--不过我建议你最好带上年份,不要只查什么7月。 select m.Department 部门名称, count(1) 迟到次数 from staff_info m , time_record n where m.Staff_code = n.Staff_code and convert(varchar(7),n.arrive_time,120) = '2010-07' and convert(varchar(5),n.arrive_time,108) > '09:40' group by m.Department having count(1) >= 3
and date between convert(datetime,convert(varchar(11),date,120)+'09:40')
and convert(datetime,convert(varchar(11),date,120)+'12:00')
表一:员工信息表(staff_info)
字段:Staff_code:员工工号;Staff_name:员工姓名;Department:所属部门
Staff_code Staff_name Department
90001 路人甲 技术部
90002 路人乙 技术部
90003 路人丙 人事部
90004 路人丁 人事部
… … … 表二:上下班记录表(time_record)
字段:Staff_code:员工工号;arrive_time:上班时间;Leave_time:下班时间)
Staff_code arrive_time Leave_time
9001 2010-07-02 09:32:33 2010-07-02 18:35:58
9001 2010-07-05 09:45:03 2010-07-05 17:50:20
9001 2010-08-03 09:55:01
9002 2010-07-09 10:13:22 2010-07-09 19:48:00
… … … 要求,使用一个SQL语句,查询出在7月份部门员工迟到3次及以上的部门,结果集包括部门名称、迟到次数。超过9点40分到达公司为迟到。
where m.Staff_code = n.Staff_code and datepart(mm,arrive_time) = 7 and convert(varchar(5),n.arrive_time,108) > '09:40'
group by m.Department
having count(1) >= 3--不过我建议你最好带上年份,不要只查什么7月。
select m.Department 部门名称, count(1) 迟到次数 from staff_info m , time_record n
where m.Staff_code = n.Staff_code and convert(varchar(7),n.arrive_time,120) = '2010-07' and convert(varchar(5),n.arrive_time,108) > '09:40'
group by m.Department
having count(1) >= 3