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 @t1col
------------------------------------------------------
2005-10-11 08:00:00.000
2005-10-11 23:00:00.000
2005-10-12 01:00:00.000(所影响的行数为 3 行)
(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 @t1col
------------------------------------------------------
2005-10-11 08:00:00.000
2005-10-11 23:00:00.000
2005-10-12 01:00:00.000(所影响的行数为 3 行)
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))
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))
方法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
userid,
min(dates) as 上班,
max(dates) as 下班
from #t
group by userid,DATENAME(yyyy,dates),DATENAME(mm,dates),DATENAME(dd,dates)