我的一个字段是日期型ztime,内容是"9:30:00",我如果查找字段为"9:30:00"的记录呢?我试过很多方法都不行,请高人指教。
select * form mdb where ztime ='9:30:00'
select * form mdb where datediff(n,ztime,'9:30:00')=0
select * form mdb where datediff(n,ztime,convert(chart(10),'9:30:00',108)=0
select * form mdb where ztime ='9:30:00'
select * form mdb where datediff(n,ztime,'9:30:00')=0
select * form mdb where datediff(n,ztime,convert(chart(10),'9:30:00',108)=0
create table #temp
(
[time] time(0) --类型可用time(0|1|2...)来确定精度
)
insert #temp
select '9:30:00'
go
--SQL:
select * from #temp where cast([time] as time(0)) = '9:30:00'
/*
time
09:30:00
*/
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int identity(1,1),
dt datetime
)
go
--插入测试数据
insert into tb select '9:30:00'
union all select '12:30:00'
union all select '15:45:23'
union all select '9:30:00'
go
--代码实现select * from tb where charindex('9:30:00',convert(varchar(19),dt,120))>0/*测试结果 (表 a)id dt
--------------------------------
1 1900-01-01 09:30:00.000
4 1900-01-01 09:30:00.000(2 行受影响)
*/
你用的是varchar类型吗?呵呵