---区别很太. declare @a table (id int,[date] datetime) insert into @a select 1,'2009-07-06 17:21:00' union all select 2,'2009-07-06 18:20:00' union all select 3,'2009-07-06 18:26:00' union all select 4,'2009-07-06 19:20:00' union all select 5,'2009-07-06 20:40:00' declare @b table (id int,[date] datetime) insert into @b select 6,'2009-07-06 17:21:00' union all select 7,'2009-07-06 18:30:00' union all select 8,'2009-07-06 18:26:00' union all select 9,'2009-07-06 20:20:00' union all select 10,'2009-07-06 20:40:00' select * from @a a join @b b on a.date=b.date select * from @a a join @b b on ABS(DATEDIFF(hh,a.date,b.date)) <1 (5 行受影响)(5 行受影响) id date id date ----------- ----------------------- ----------- ----------------------- 1 2009-07-06 17:21:00.000 6 2009-07-06 17:21:00.000 3 2009-07-06 18:26:00.000 8 2009-07-06 18:26:00.000 5 2009-07-06 20:40:00.000 10 2009-07-06 20:40:00.000(3 行受影响)id date id date ----------- ----------------------- ----------- ----------------------- 1 2009-07-06 17:21:00.000 6 2009-07-06 17:21:00.000 2 2009-07-06 18:20:00.000 7 2009-07-06 18:30:00.000 3 2009-07-06 18:26:00.000 7 2009-07-06 18:30:00.000 2 2009-07-06 18:20:00.000 8 2009-07-06 18:26:00.000 3 2009-07-06 18:26:00.000 8 2009-07-06 18:26:00.000 5 2009-07-06 20:40:00.000 9 2009-07-06 20:20:00.000 5 2009-07-06 20:40:00.000 10 2009-07-06 20:40:00.000(7 行受影响)
---区别很大 declare @a table (id int,[date] datetime) insert into @a select 1,'2009-07-06 17:21:00' union all select 2,'2009-07-06 18:20:00' union all select 3,'2009-07-06 18:26:00' union all select 4,'2009-07-06 19:20:00' union all select 5,'2009-07-06 20:40:00' declare @b table (id int,[date] datetime) insert into @b select 6,'2009-07-06 17:21:00' union all select 7,'2009-07-06 18:30:00' union all select 8,'2009-07-06 18:26:00' union all select 9,'2009-07-06 20:20:00' union all select 10,'2009-07-06 20:40:00' select * from @a a join @b b on a.date=b.date select * from @a a join @b b on ABS(DATEDIFF(hh,a.date,b.date)) <1 (5 行受影响)(5 行受影响) id date id date ----------- ----------------------- ----------- ----------------------- 1 2009-07-06 17:21:00.000 6 2009-07-06 17:21:00.000 3 2009-07-06 18:26:00.000 8 2009-07-06 18:26:00.000 5 2009-07-06 20:40:00.000 10 2009-07-06 20:40:00.000(3 行受影响)id date id date ----------- ----------------------- ----------- ----------------------- 1 2009-07-06 17:21:00.000 6 2009-07-06 17:21:00.000 2 2009-07-06 18:20:00.000 7 2009-07-06 18:30:00.000 3 2009-07-06 18:26:00.000 7 2009-07-06 18:30:00.000 2 2009-07-06 18:20:00.000 8 2009-07-06 18:26:00.000 3 2009-07-06 18:26:00.000 8 2009-07-06 18:26:00.000 5 2009-07-06 20:40:00.000 9 2009-07-06 20:20:00.000 5 2009-07-06 20:40:00.000 10 2009-07-06 20:40:00.000(7 行受影响)
处二个精确到hh
---区别很太.
declare @a table (id int,[date] datetime)
insert into @a select 1,'2009-07-06 17:21:00'
union all select 2,'2009-07-06 18:20:00'
union all select 3,'2009-07-06 18:26:00'
union all select 4,'2009-07-06 19:20:00'
union all select 5,'2009-07-06 20:40:00'
declare @b table (id int,[date] datetime)
insert into @b select 6,'2009-07-06 17:21:00'
union all select 7,'2009-07-06 18:30:00'
union all select 8,'2009-07-06 18:26:00'
union all select 9,'2009-07-06 20:20:00'
union all select 10,'2009-07-06 20:40:00'
select * from @a a join @b b on a.date=b.date
select * from @a a join @b b on ABS(DATEDIFF(hh,a.date,b.date)) <1 (5 行受影响)(5 行受影响)
id date id date
----------- ----------------------- ----------- -----------------------
1 2009-07-06 17:21:00.000 6 2009-07-06 17:21:00.000
3 2009-07-06 18:26:00.000 8 2009-07-06 18:26:00.000
5 2009-07-06 20:40:00.000 10 2009-07-06 20:40:00.000(3 行受影响)id date id date
----------- ----------------------- ----------- -----------------------
1 2009-07-06 17:21:00.000 6 2009-07-06 17:21:00.000
2 2009-07-06 18:20:00.000 7 2009-07-06 18:30:00.000
3 2009-07-06 18:26:00.000 7 2009-07-06 18:30:00.000
2 2009-07-06 18:20:00.000 8 2009-07-06 18:26:00.000
3 2009-07-06 18:26:00.000 8 2009-07-06 18:26:00.000
5 2009-07-06 20:40:00.000 9 2009-07-06 20:20:00.000
5 2009-07-06 20:40:00.000 10 2009-07-06 20:40:00.000(7 行受影响)
declare @a table (id int,[date] datetime)
insert into @a select 1,'2009-07-06 17:21:00'
union all select 2,'2009-07-06 18:20:00'
union all select 3,'2009-07-06 18:26:00'
union all select 4,'2009-07-06 19:20:00'
union all select 5,'2009-07-06 20:40:00'
declare @b table (id int,[date] datetime)
insert into @b select 6,'2009-07-06 17:21:00'
union all select 7,'2009-07-06 18:30:00'
union all select 8,'2009-07-06 18:26:00'
union all select 9,'2009-07-06 20:20:00'
union all select 10,'2009-07-06 20:40:00'
select * from @a a join @b b on a.date=b.date
select * from @a a join @b b on ABS(DATEDIFF(hh,a.date,b.date)) <1 (5 行受影响)(5 行受影响)
id date id date
----------- ----------------------- ----------- -----------------------
1 2009-07-06 17:21:00.000 6 2009-07-06 17:21:00.000
3 2009-07-06 18:26:00.000 8 2009-07-06 18:26:00.000
5 2009-07-06 20:40:00.000 10 2009-07-06 20:40:00.000(3 行受影响)id date id date
----------- ----------------------- ----------- -----------------------
1 2009-07-06 17:21:00.000 6 2009-07-06 17:21:00.000
2 2009-07-06 18:20:00.000 7 2009-07-06 18:30:00.000
3 2009-07-06 18:26:00.000 7 2009-07-06 18:30:00.000
2 2009-07-06 18:20:00.000 8 2009-07-06 18:26:00.000
3 2009-07-06 18:26:00.000 8 2009-07-06 18:26:00.000
5 2009-07-06 20:40:00.000 9 2009-07-06 20:20:00.000
5 2009-07-06 20:40:00.000 10 2009-07-06 20:40:00.000(7 行受影响)