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()
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供参考!
RAND() * 30 from (select 1 c )x
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()
参数 #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)
select @dt1='19:30',@dt2='20:00'
select convert(varchar(5),dateadd(n,abs(checksum(newid()))%datediff(n,@dt1,@dt2),@dt1),8)
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()
提示
消息 2715,级别 16,状态 3,第 3 行
第 1 个列、参数或变量: 找不到数据类型 time。
参数或变量 '@dt2' 的数据类型无效。
消息 2715,级别 16,状态 3,第 3 行
第 2 个列、参数或变量: 找不到数据类型 time。
参数或变量 '@dt1' 的数据类型无效。我的是sql2005
@etime time
select @stime='19:30',
@etime='20:30'
select dateadd(second,datediff(second,@stime,@etime)*rand(),@stime )