产生随机时间
19:30 和20:00两个参数
每次执行都得到不同的时间(大于19:30,小于20:30)
------解决思路----------------------
select top 1 substring(convert(char,DATEADD(mi,number,dates),120),12,5) as n
from (select number from master..spt_values t
where t.type='p' and number>0 and number<=60) as p cross join (select '19:30' as dates) as t
order by NEWID()
------解决思路----------------------
if exists (select * from sys.objects where name='randint')
drop procedure randint
go
create procedure randint @begin int, @end int, @result int output
as
begin
select @result = @begin+floor(rand()*(@end-@begin+1))
--rand() 返回一个介于 0 到 1（不包括 0 和 1）之间的伪随机 float 值
end
go
-- 调用的例子
declare @rand1 int
declare @rand2 int
exec randint 0, 30, @rand1 output
exec randint 30, 60, @rand2 output
select '19'+':'+ cast(@rand2 as nvarchar(4))
select '20'+':'+ cast(@rand1 as nvarchar(4))
go
供参考！
------解决思路----------------------
declare @s varchar(10)
declare @s1 varchar(10)
set @s='19:30'
set @s1='20:00'
select top 1 convert(varchar(5), dateadd(second,number,cast (( CONVERT(varchar(100), GETDATE(), 23)+' '+@s) as datetime )),24) from master..spt_values
where type='p' and number>=1
and dateadd(second,number,cast (( CONVERT(varchar(100), GETDATE(), 23)+' '+@s) as datetime ))<=cast (( CONVERT(varchar(100), GETDATE(), 23)+' '+@s1) as datetime )
order by newid()
------解决思路----------------------
可以把20：00也用进去
参数 #1 修改如下
select top 1 substring(convert(char,DATEADD(mi,number,dates),120),12,5) as n
from (select number from master..spt_values t
where t.type='p' and number>0 and number<DATEDIFF(MI,'19:30','21:00')) as p cross join (select '19:30' as dates) as t
order by NEWID()
------解决思路----------------------
select left(cast(dateadd(mi,floor(rand()*(DATEDIFF(MI,cast('07:30' as time), cast('08:00' as time)))) ,cast('07:30' as time)) as varchar),5)
------解决思路----------------------
declare @dt1 time,@dt2 time
select @dt1='19:30',@dt2='20:00'
select convert(varchar(5),dateadd(n,abs(checksum(newid()))%datediff(n,@dt1,@dt2),@dt1),8)
------解决思路----------------------
先取相差分钟数，再用随机系统函数实现
------解决思路----------------------
declare @dt1 time,@dt2 time
select @dt1='23:50',@dt2='00:01'
select convert(varchar(5),dateadd(n,abs(checksum(newid()))%datediff(n,@dt1,@dt2),@dt1),8) FROM sys.tables
这样写有些小问题，就是不能跨天
-----
20:03
03:47
01:43
23:21
05:21
13:07
23:57
00:53
15:32
08:19
(10 行受影响)
------解决思路----------------------
select top 1 substring(convert(char,DATEADD(mi,number,dates),120),12,5) as n
from (select number from master..spt_values t
where t.type='p' and number>0 and number<=datediff(mi,@date,@date2) as p cross join (select '19:30' as dates) as t
order by NEWID()
------解决思路----------------------
declare @stime time,
@etime time
select @stime='19:30',
@etime='20:30'
select dateadd(second,datediff(second,@stime,@etime)*rand(),@stime )