34,593
社区成员
发帖
与我相关
我的任务
分享
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 行受影响)
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 @stime time,
@etime time
select @stime='19:30',
@etime='20:30'
select dateadd(second,datediff(second,@stime,@etime)*rand(),@stime )
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()
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()
-- 19:30 加上 0 - 30 分钟的随机数
RAND() * 30 from (select 1 c )x
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()
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
供参考!