数据库表T_time
begintime endtime
0 8:00:00 16:00:00
1 16:00:00 0:00:00
2 0:00:00 8:00:00比如我现在已知电脑时间为 9:00:00如何查出第一条记录?比如已知时间为17:00:00,如何查出第二条记录??
select * from t_time where begintime<='9:00:00' and endtime>'9:00:00'
或 select * from t_time '9:00:00' between begintime and endtime 都不可以呀,怎么整??
begintime endtime
0 8:00:00 16:00:00
1 16:00:00 0:00:00
2 0:00:00 8:00:00比如我现在已知电脑时间为 9:00:00如何查出第一条记录?比如已知时间为17:00:00,如何查出第二条记录??
select * from t_time where begintime<='9:00:00' and endtime>'9:00:00'
或 select * from t_time '9:00:00' between begintime and endtime 都不可以呀,怎么整??
select * from tb where convert(varchar(8),getdate(),114) >= begintime and convert(varchar(8),getdate(),114) <= endtime
insert into tb values(0, '08:00:00', '16:00:00')
insert into tb values(1, '16:00:00', '24:00:00')
insert into tb values(2, '00:00:00', '08:00:00')
goselect * from tb where convert(varchar(8),getdate(),114) >= begintime and convert(varchar(8),getdate(),114) <= endtime
drop table tb/*
begintime endtime
----------- --------- --------
0 08:00:00 16:00:00(所影响的行数为 1 行)
*/
1 16:00:00 0:00:00
2 0:00:00 8:00:00
数据库返回所有行,就是这样显示的。没错吧。
begintime,endtime是datetime类型。
declare @T_time table( int,begintime datetime,endtime datetime)
insert @T_time
select 0,'8:00:00','16:00:00' union all
select 1,'16:00:00','0:00:00' union all
select 2,'0:00:00','8:00:00'--看返回结果集:
select * from @T_time/*
begintime endtime
0 1900-01-01 08:00:00.000 1900-01-01 16:00:00.000
1 1900-01-01 16:00:00.000 1900-01-01 00:00:00.000
2 1900-01-01 00:00:00.000 1900-01-01 08:00:00.000
*/
insert @T_time
select 0,'8:00:00','16:00:00' union all
select 1,'16:00:00','0:00:00' union all
select 2,'0:00:00','8:00:00'--看看smalldatetime
select * from @T_time/*
begintime endtime
0 1900-01-01 08:00:00 1900-01-01 16:00:00
1 1900-01-01 16:00:00 1900-01-01 00:00:00
2 1900-01-01 00:00:00 1900-01-01 08:00:00
*/
select * from test
where convert(varchar(8),getdate(),114) between begintime and endtime
7楼,我用的是sql server 2000,建表都是可视化的,很简单。插入数据就更简单了,直接输入就可以了。
,数据类型为datetime,显示确实是
begintime endtime
0 8:00:00 16:00:00
1 16:00:00 0:00:00
2 0:00:00 8:00:00
您搞得太复杂了,现在我要问的答案类似于8楼的答案:
如果都是时间型.
select * from t_time where datepart(hour,getdate()) >= datepart(hour,bigintime) and datepart(hour,getdate()) < datepart(hour,endtime)但是如果数据是:
begintime endtime
0 7:50:00 15:50:00
1 15:50:00 23:50:00
2 23:50:00 0:00:00
2 0:00:00 7:50:00
那该怎么写呢??
SELECT *
FROM t_time
WHERE (GETDATE() > BEGINTIME) AND (GETDATE() < ENDTIME)不行
SELECT *
FROM t_time
WHERE ('9:00:00' > BEGINTIME) AND ('9:00:00'< ENDTIME)更不行
FROM t_time
WHERE (convert(varchar,GETDATE(),114) > convert(varchar,BEGINTIME,114) AND (convert(varchar,GETDATE(),114) < convert(varchar,ENDTIME,114))
--行SELECT *
FROM t_time
WHERE (convert(varchar,'9:00:00',114) > convert(varchar,BEGINTIME,114) AND (convert(varchar,'9:00:00',114) < convert(varchar,ENDTIME,114))
--也行
数据库表T_time
begintime endtime
0 08:00:00 15:59:59
1 16:00:00 23:59:59
2 00:00:00 07:59:59
如果都是时间型. select * from t_time where datepart(hour,getdate()) >= datepart(hour,bigintime) and datepart(hour,getdate()) <= datepart(hour,endtime)