跪求一条时间的查询语句

[reynal]

有几个月的考勤的数据，每条都有格式为年月日时分的时间记录，每一条代表一次进或出的记录，现想求一个月中每天的最早的一条和最迟的一条时间记录输出到一个表。请问应该怎么写？谢谢！！

[samfeng_2003]

declare @t table
(col datetime)

declare @t1 table
(col datetime)
insert @t
select '2005-10-11 12:00:00' union all
select '2005-10-11 23:00:00' union all
select '2005-10-11 08:00:00' union all
select '2005-10-12 01:00:00'

insert @t1
--求一天中的最小的时间
select * from @t a where
not exists (select * from @t b where convert(char(10),a.col,120)
=convert(char(10),b.col,120) and a.col>b.col)
union
---求一天中的最大时间
select * from @t a where
not exists (select * from @t b where convert(char(10),a.col,120)
=convert(char(10),b.col,120) and a.col<b.col)

select * from @t1

col
------------------------------------------------------
2005-10-11 08:00:00.000
2005-10-11 23:00:00.000
2005-10-12 01:00:00.000

（所影响的行数为 3 行）

[lsqkeke]

如果输出的那个表(dtt)已经建立好 则有：
insert into dtt
select * from tb1 where dtime=(select mintime=min(dtime) from tb1 group by convert(varchar(10),dtime,120))
union
select * from tb1 where dtime=(select mintime=max(dtime) from tb1 group by convert(varchar(10),dtime,120))

[ReViSion]

如果输出的那个表(dtt)已经建立好 则有：
insert into dtt
select * from tb1 where dtime=(select mintime=min(dtime) from tb1
where dtime between '月初日期' and '月未日期'
group by convert(varchar(10),dtime,120) )
union
select * from tb1 where dtime=(select mintime=max(dtime) from tb1
where dtime between '月初日期' and '月未日期'
group by convert(varchar(10),dtime,120))

[-狙击手-]

select
userid,
min(dates) as 上班,
max(dates) as 下班
from #t
group by userid,DATENAME(yyyy,dates),DATENAME(mm,dates),DATENAME(dd,dates)

[reynal]

太感动了，大家真热诚

[玩不够]

我提供两种方法
方法1是 0点 到 次日0点前的常用方法
方法2是 指定起始点 常用在加班超过次日0点的情况，
我公司用了几年了，效果不错，现公布和大家分享
create table #t
(
userid smallint,
dates datetime
)
insert into #t values (1,'2006-1-1 08:15:50')
insert into #t values (1,'2006-1-1 09:25:50')
insert into #t values (1,'2006-1-1 18:35:50')
insert into #t values (1,'2006-1-2 03:10:50')
insert into #t values (1,'2006-1-2 09:25:50')
insert into #t values (1,'2006-1-2 18:35:50')
insert into #t values (1,'2006-1-5 08:45:50')
insert into #t values (1,'2006-1-5 09:55:50')
insert into #t values (1,'2006-1-5 18:15:50')
insert into #t values (1,'2006-1-6 08:20:50')
insert into #t values (1,'2006-1-6 09:35:50')
insert into #t values (1,'2006-1-6 18:45:50')
insert into #t values (3,'2006-1-1 08:55:50')
insert into #t values (3,'2006-1-1 09:15:50')
insert into #t values (3,'2006-1-1 18:25:50')
insert into #t values (3,'2006-1-2 05:59:59')
insert into #t values (3,'2006-1-2 09:45:50')
insert into #t values (3,'2006-1-2 18:55:50')
insert into #t values (3,'2006-1-5 08:15:50')
insert into #t values (3,'2006-1-5 09:25:50')
insert into #t values (3,'2006-1-5 18:35:50')
insert into #t values (3,'2006-1-6 08:40:50')
insert into #t values (3,'2006-1-6 09:55:50')
insert into #t values (3,'2006-1-6 18:15:50')

select
userid,
min(dates) as 上班,
max(dates) as 下班
from #t
group by userid,DATENAME(yyyy,dates),DATENAME(mm,dates),DATENAME(dd,dates)


select
a.userid,
min(a.dates) as 上班,
max(a.dates) as 下班
from
(
SELECT
a.userid,
a.dates,
case when DATEDIFF(hh,date1,dates) > =6
then a.dates
else DATEADD(dd,-1,a.dates) end as date2
FROM
(
SELECT userid,
DATENAME(yyyy,dates)+'-'
+DATENAME(mm,dates)+'-'
+DATENAME(dd,dates) as date1,
dates
FROM #t
) a
) a
group by
a.userid,
DATENAME(yyyy,date2)+DATENAME(mm,date2)+DATENAME(dd,date2)
drop table #t


userid 上班 下班
------ --------------------------- ----------------------------
1 2006-01-01 08:15:50.000 2006-01-01 18:35:50.000
1 2006-01-02 03:10:50.000 2006-01-02 18:35:50.000
1 2006-01-05 08:45:50.000 2006-01-05 18:15:50.000
1 2006-01-06 08:20:50.000 2006-01-06 18:45:50.000
3 2006-01-01 08:55:50.000 2006-01-01 18:25:50.000
3 2006-01-02 05:59:59.000 2006-01-02 18:55:50.000
3 2006-01-05 08:15:50.000 2006-01-05 18:35:50.000
3 2006-01-06 08:40:50.000 2006-01-06 18:15:50.000

（所影响的行数为 8 行）

userid 上班 下班
------ -------------------------- ------------------------------------
1 2006-01-01 08:15:50.000 2006-01-02 03:10:50.000
1 2006-01-02 09:25:50.000 2006-01-02 18:35:50.000
1 2006-01-05 08:45:50.000 2006-01-05 18:15:50.000
1 2006-01-06 08:20:50.000 2006-01-06 18:45:50.000
3 2006-01-01 08:55:50.000 2006-01-02 05:59:59.000
3 2006-01-02 09:45:50.000 2006-01-02 18:55:50.000
3 2006-01-05 08:15:50.000 2006-01-05 18:35:50.000
3 2006-01-06 08:40:50.000 2006-01-06 18:15:50.000