表tb1結構：
f1 int 自增
f2 datetime
f2的值大致如下：
2006-12-20 22:22:32.000
2006-12-20 23:32:38.000
2006-12-20 00:24:49.000
declare t1 char(5),
t2 char(5),
t3 datetime
set t1= '23:30 '
set t1= '00:30 ' 第二天的l凌晨時間
現在請教如何在表tb1中查詢出字段f2時間部分在t1至t2范圍的紀錄
------解决方案--------------------
select * from tablename where f2 between cast(( '2006-12-20 '+ '23:30 '+ ':000 ') as datetime) and cast(( '2006-12-21 '+ '00:30 '+ ':000 ') as datetime)
------解决方案--------------------
select * from tablename
where convert(varchar(10),f2,108) between '23:30 ' and '00:30 '
------解决方案--------------------
create table T(f1 int, f2 datetime)
insert T select 1, '2006-12-20 22:22:32.000 '
union all select 2, '2006-12-20 23:32:38.000 '
union all select 3, '2006-12-20 00:24:49.000 '
declare @t1 char(5), @t2 char(5)
set @t1= '23:30 '
set @t2= '00:30 '
select * from T
where f2 between (convert(char(10), f2, 120)+ ' '[email protected]) and (convert(char(10), f2+1, 120)+ ' '[email protected])
--result
f1 f2
----------- ------------------------------------------------------
2 2006-12-20 23:32:38.000
(1 row(s) affected)
------解决方案--------------------
create table T(f1 int, f2 datetime)
insert T select 1, '2006-12-20 22:22:32.000 '
union all select 2, '2006-12-20 23:32:38.000 '
union all select 3, '2006-12-20 00:24:49.000 '
select f2 from T where
DATEDIFF(Hour, Convert(char(8),f2,112),f2) in (23,0)
and
(DATEDIFF(Minute, Convert(char(10),f2,112),f2)-DATEDIFF(Hour, Convert(char(8),f2,112),f2)*60)
between 0 and 59
------解决方案--------------------
create table T(f1 int, f2 datetime)
insert T select 1, '2006-12-20 22:22:32.000 '
union all select 2, '2006-12-20 23:32:38.000 '
union all select 3, '2006-12-20 00:24:49.000 '
union all select 4, '2006/12/28 00:34:11.000 '
select f2 from T where
(DATEDIFF(Hour, Convert(char(8),f2,112),f2)=23
and
(DATEDIFF(Minute, Convert(char(10),f2,112),f2)-DATEDIFF(Hour, Convert(char(8),f2,112),f2)*60)
between 0 and 59)
or
(DATEDIFF(Hour, Convert(char(8),f2,112),f2)=0
and
(DATEDIFF(Minute, Convert(char(10),f2,112),f2)-DATEDIFF(Hour, Convert(char(8),f2,112),f2)*60)
between 0 and 30)
------解决方案--------------------
字段f2時間部分在t1至t2范圍的紀錄?LZ 你表達的不是很清楚.
2006-12-20 00:24:49.000
2006-12-20 23:34:49.000
2006-12-19 23:34:49.000
2006-12-19 00:24:49.000
時間在 00:30 和 23:30 中的記錄是?