create table tb(dt varchar(5)) insert into tb values('09:00') insert into tb values('10:00') insert into tb values('11:00') insert into tb values('12:00') insert into tb values('13:00') insert into tb values('14:00') insert into tb values('15:00') insert into tb values('16:00') insert into tb values('17:00') insert into tb values('18:00') goselect top 1 dt from tb order by newid()drop table tb/* dt ----- 15:00(所影响的行数为 1 行) */
select top 1 时间=ltrim(a.number)+':00' from master..spt_values a where a.type='p' and a.number between 9 and 18 order by newid()
declare @sdate datetime declare @edate datetime set @sdate = convert(varchar(11),getdate(),120) + '09:00:000' set @edate = convert(varchar(11),getdate(),120) + '18:00:000'select top 1 convert(varchar(8),dt,108) from ( select dateadd(mi,num,@sdate) dt from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(mi,num,@sdate)<=@edate ) t order by newid()/*
-------- 09:53:00(所影响的行数为 1 行)*/
select top 1 时间=ltrim(a.number)+':'+right('0'+ltrim(cast(rand()*60 as int)),2) from master..spt_values a where a.type='p' and a.number between 9 and 18 order by newid()/* 时间 ----------------- 16:23(1 行受影响) 时间 ----------------- 12:28(1 行受影响) 时间 ----------------- 12:44(1 行受影响) */
DECLARE @min varchar(10),@max varchar(10); SELECT @min='09:00',@max = '18:00'; SELECT CONVERT(varchar(5),DATEADD(minute,ABS(CHECKSUM(NEWID())) % (DATEDIFF(minute,CAST(@min AS datetime),CAST(@max AS datetime))) ,@min),108)
select top 100000 id=identity(int,1,1) into # from sys.objects,sys.columns declare @start varchar(5),@end varchar(5) select @start='09:00',@end='18:00' select top 1 convert(varchar(8),dateadd(ss,id,convert(varchar(10),getdate(),120)+' '+@start+':00'),108) from # where dateadd(ss,id,convert(varchar(10),getdate(),120)+' '+@start+':00')<=convert(varchar(10),getdate(),120)+' '+@end+':00' order by newid()drop table #/**-------- 14:12:27(1 行受影响) **/ 不要秒的话把varchar(8)改为varchar(5)
declare @sdate datetime declare @edate datetime set @sdate = convert(varchar(11),getdate(),120) + '09:00:000' set @edate = convert(varchar(11),getdate(),120) + '18:00:000'--要小时的。 select top 1 convert(varchar(8),dt,108) from ( select dateadd(hh,num,@sdate) dt from (select isnull((select count(1) from sysobjects t1 where t1.id<t.id),0) as num from sysobjects t) a where dateadd(hh,num,@sdate)<=@edate ) t order by newid() /*
-------- 11:00:00(所影响的行数为 1 行) */--要分钟的。 select top 1 convert(varchar(8),dt,108) from ( select dateadd(mi,num,@sdate) dt from (select isnull((select count(1) from sysobjects t1,sysobjects t2 where t1.id<t.id),0) as num from sysobjects t) a where dateadd(mi,num,@sdate)<=@edate ) t order by newid()/* -------- 12:36:00(所影响的行数为 1 行)*/--要秒的。 select top 1 convert(varchar(8),dt,108) from ( select dateadd(ss,num,@sdate) dt from (select isnull((select count(1) from sysobjects t1,sysobjects t2,sysobjects t3 where t1.id<t.id),0) as num from sysobjects t) a where dateadd(ss,num,@sdate)<=@edate ) t order by newid()/*
-------- 17:38:24(所影响的行数为 1 行) */
declare @d as datetime select @d='1900-1-1' select t=substring(convert(varchar(16),dateadd(minute,abs(checksum(newid()))%540+540,@d),8),1,5) /* t ---------- 09:25 */
select top 1 时间=ltrim(a.number)+':00'
from master..spt_values a
where a.type='p'
and a.number between 9 and 18
order by newid()
@endtime datetime --结束时间
select @starttime='2005-08-01 06:00:00',
@endtime='2005-08-01 08:00:00' select dateadd( second, convert(int,(rand()*datediff(second,@starttime,@endtime))), @starttime )
insert into tb values('09:00')
insert into tb values('10:00')
insert into tb values('11:00')
insert into tb values('12:00')
insert into tb values('13:00')
insert into tb values('14:00')
insert into tb values('15:00')
insert into tb values('16:00')
insert into tb values('17:00')
insert into tb values('18:00')
goselect top 1 dt from tb order by newid()drop table tb/*
dt
-----
15:00(所影响的行数为 1 行)
*/
select top 1 时间=ltrim(a.number)+':00'
from master..spt_values a
where a.type='p'
and a.number between 9 and 18
order by newid()
/*
时间
---------------
15:00(1 行受影响)时间
---------------
14:00(1 行受影响)时间
---------------
16:00(1 行受影响)
时间
---------------
10:00(1 行受影响)*/
@endtime datetime --结束时间
select @starttime='2005-08-01 09:00:00',
@endtime='2005-08-01 18:00:00' select convert(varchar(10),dateadd( second, convert(int,(rand()*datediff(second,@starttime,@endtime))), @starttime ),108)
declare @edate datetime
set @sdate = convert(varchar(11),getdate(),120) + '09:00:000'
set @edate = convert(varchar(11),getdate(),120) + '18:00:000'select top 1 convert(varchar(8),dt,108) from
(
select
dateadd(mi,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(mi,num,@sdate)<=@edate
) t
order by newid()/*
--------
09:53:00(所影响的行数为 1 行)*/
select top 1 时间=ltrim(a.number)+':'+right('0'+ltrim(cast(rand()*60 as int)),2)
from master..spt_values a
where a.type='p'
and a.number between 9 and 18
order by newid()/*
时间
-----------------
16:23(1 行受影响)
时间
-----------------
12:28(1 行受影响)
时间
-----------------
12:44(1 行受影响)
*/
SELECT @min='09:00',@max = '18:00';
SELECT
CONVERT(varchar(5),DATEADD(minute,ABS(CHECKSUM(NEWID()))
% (DATEDIFF(minute,CAST(@min AS datetime),CAST(@max AS datetime)))
,@min),108)
dateadd
(second,convert(int,rand(datepart(millisecond,getdate()))*10000)%1200,
dateadd(hour,9,convert(int,18)))
dateadd
(second,convert(int,rand(datepart(millisecond,getdate()))*10000)%1200,
dateadd(hour,9,convert(int,getdate())))
select top 100000 id=identity(int,1,1) into # from sys.objects,sys.columns declare @start varchar(5),@end varchar(5)
select @start='09:00',@end='18:00'
select
top 1
convert(varchar(8),dateadd(ss,id,convert(varchar(10),getdate(),120)+' '+@start+':00'),108)
from
#
where
dateadd(ss,id,convert(varchar(10),getdate(),120)+' '+@start+':00')<=convert(varchar(10),getdate(),120)+' '+@end+':00'
order by
newid()drop table #/**--------
14:12:27(1 行受影响)
**/
不要秒的话把varchar(8)改为varchar(5)
declare @edate datetime
set @sdate = convert(varchar(11),getdate(),120) + '09:00:000'
set @edate = convert(varchar(11),getdate(),120) + '18:00:000'--要小时的。
select top 1 convert(varchar(8),dt,108) from
(
select
dateadd(hh,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects t1 where t1.id<t.id),0) as num from sysobjects t) a
where
dateadd(hh,num,@sdate)<=@edate
) t
order by newid()
/*
--------
11:00:00(所影响的行数为 1 行)
*/--要分钟的。
select top 1 convert(varchar(8),dt,108) from
(
select
dateadd(mi,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects t1,sysobjects t2 where t1.id<t.id),0) as num from sysobjects t) a
where
dateadd(mi,num,@sdate)<=@edate
) t
order by newid()/*
--------
12:36:00(所影响的行数为 1 行)*/--要秒的。
select top 1 convert(varchar(8),dt,108) from
(
select
dateadd(ss,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects t1,sysobjects t2,sysobjects t3 where t1.id<t.id),0) as num from sysobjects t) a
where
dateadd(ss,num,@sdate)<=@edate
) t
order by newid()/*
--------
17:38:24(所影响的行数为 1 行)
*/
select @d='1900-1-1'
select t=substring(convert(varchar(16),dateadd(minute,abs(checksum(newid()))%540+540,@d),8),1,5) /*
t
----------
09:25
*/