create table clocking
(
ID int identity(1,1),
Name nvarchar(20),
CheckTime datetime)
insert clocking (Name,CheckTime)
select 'lieshengren ', '2004-1-1 10:00 '
union
select 'lieshengren ', '2004-1-1 11:00 '
union
select 'lieshengren ', '2004-1-1 12:00 '
union
select 'lieshengren ', '2005-1-1 10:00 '
select * from clocking
select Name,min(CheckTime) as BeginDatetime,max(CheckTime) as EndDatetime from clocking group by name,convert(char(10),CheckTime,120)
drop table clocking
控制台如下:
(4 row(s) affected)
ID Name CheckTime
----------- -------------------- -----------------------
1 lieshengren 2004-01-01 10:00:00.000
2 lieshengren 2004-01-01 11:00:00.000
3 lieshengren 2004-01-01 12:00:00.000
4 lieshengren 2005-01-01 10:00:00.000
(4 row(s) affected)
Name BeginDatetime EndDatetime
-------------------- ----------------------- -----------------------
lieshengren 2004-01-01 10:00:00.000 2004-01-01 12:00:00.000
lieshengren 2005-01-01 10:00:00.000 2005-01-01 10:00:00.000
(2 row(s) affected)