求简单的时间代码问题
表格如下:
id beg end
1 9:01 11:12
2 12:35 13:11
.. ... ...
给一个时间比如 10:22, 得出id=1
或者 12:55/id=2
[解决办法]
declare @a table(id int, beg varchar(10), [end] varchar(10))
insert @a select 1 , '9:01 ', '11:12 '
union all select 2 , '12:35 ', '13:11 '
declare @s varchar(10)
set @s= '9:03 '
select * from @a where right( '0 '+@s,2) between
right( '0 '+beg,2) and right( '0 '+[end],2)
[解决办法]
alter procedure P_getTimeId(@time as varchar(10))
as
begin
declare @a table(id int,beg varchar(10),[end] varchar(10))
insert @a select 1, '9:01 ', '11:12 '
union all select 2, '12:35 ', '13:11 '
--select * from @a
declare @s varchar(10)
declare @beg varchar(10),@end varchar(10)
declare CrsA cursor
for select beg,[end] from @a order by id
open CrsA
--set @s= '12:55 '
Fetch next from CrsA into @beg,@end
while @@FETCH_STATUS=0
begin
select id from @a where cast(stuff(@time,3,1, '0 ') as int)
between cast(stuff( ' '+@beg+ ' ',charindex( ': ', ' '+@beg+ ' '),1, '0 ') as int)
and cast(stuff( ' '+@end+ ' ',charindex( ': ', ' '+@end+ ' '),1, '0 ') as int) and beg=@beg
and [end]=@end
Fetch next from CrsA into @beg,@end
end
close CrsA
deallocate CrsA
end
exec P_getTimeId '10:55 '
借用一下楼上那位的代码！
[解决办法]
哈哈,楼上的太精彩了吧,我认为可以这样
假设表名tb
create function GetId(@a varchar(20)) returns int
as
begin
declare @id int
select @id=id from tb
where cast( '2000/01/01 '+rtrim(beg)+ ':00 ' as datetime) <( '2000/01/01 '+rtrim(@a)+ ':00 ')
and cast( '2000/01/01 '+rtrim([end])+ ':00 ' as datetime)> ( '2000/01/01 '+rtrim(@a)+ ':00 ')
return isnull(@id,0)
end
执行
select dbo.GetId( '10:22 ')
