要求在一表中插入这一时间段 > '2006-05-04 19:05' < '2006-05-04 19:15' 范围内的随机时间
如:id time
01 2006-05-04 19:06
02 2006-05-04 19:08
03 2006-05-04 19:11
04 2006-05-04 19:07
... .... ...
... ....
如:id time
01 2006-05-04 19:06
02 2006-05-04 19:08
03 2006-05-04 19:11
04 2006-05-04 19:07
... .... ...
... ....
解决方案 »
- 求一个sql语句的优化写法
- 如何提取文本文件中的部分文字及带分隔符的表格
- SQL简单查询问题求解~!
- 问个sql left join问题
- 如何求某个时间段范围内不是星期六星期天的天数?急!谢谢!
- 请问怎么反.sql文件导入到sql server
- sql中and 与 or的问题
- 我想把系统格式化重装,请问sqlserver数据库的数据怎么备份最方便,安全。
- 用ADO读记录时,如果某记录被已被锁,则Recordset打开超时并且为空......?
- access 中可以 select * from table1 where adddate = #2001-10-27# 么?
- 求助一个数据库导出的问题!
- 使用CCProxy代理上网的机器如何连接公网的SQLSERVER?
Create Table #T
([time] Varchar(16))
Insert #T Select '2006-05-04 19:05'
Union All Select '2006-05-04 19:06'
Union All Select '2006-05-04 19:07'
Union All Select '2006-05-04 19:08'
Union All Select '2006-05-04 19:09'
Union All Select '2006-05-04 19:10'
Union All Select '2006-05-04 19:11'
Union All Select '2006-05-04 19:12'
Union All Select '2006-05-04 19:13'
Union All Select '2006-05-04 19:14'
Union All Select '2006-05-04 19:15'
GO
Insert TEST Select Top 1 '01',[time] From #T Order By NewID()
Insert TEST Select Top 1 '02',[time] From #T Order By NewID()
Insert TEST Select Top 1 '03',[time] From #T Order By NewID()
GO
Select * From TEST
GO
Drop Table TEST,#T
--Result
/*
ID time
01 2006-05-04 19:12
02 2006-05-04 19:11
03 2006-05-04 19:09
*/
select dateadd(minute,rand(checksum(newid()))*10,'2006-05-04 19:05:00') --解释一下
10为10分钟
minute为时间单位
后面的2006-05-04 19:05:00是时间起始时间
Create Table TEST(ID Char(2),[time] Varchar(16))
GO
Select TOP 100 ID=Identity(Int,1,1) Into # From SysColumns,SysObjects
Select Convert(Varchar(16),DateAdd(mi,ID,'2006-05-04 19:04'),120) As [time] Into #T From # Where ID<=11
Select * From #T
Insert TEST Select Top 1 '01',[time] From #T Order By NewID()
Insert TEST Select Top 1 '02',[time] From #T Order By NewID()
Insert TEST Select Top 1 '03',[time] From #T Order By NewID()
GO
Select * From TEST
GO
Drop Table TEST,#T,#
--Result
/*
ID time
01 2006-05-04 19:12
02 2006-05-04 19:11
03 2006-05-04 19:09
*/
--38839.7951select cast(cast('2006-05-04 19:15' as datetime) as money)
--38839.80212.然后产生38839.7951到38839.8021之间的随即数就可以达到目的了.--插入1000个此时间段的数值.create table #t([id] int,[time] datetime)
Go
declare @i int
set @i=1while(@i<=1000)
begin
insert #t
select @i,cast(38839+(7951+cast(rand()*70 as int))*1.0000/10000 as datetime)
set @i=@i+1
end
'2006-05-04 19:15') AS int), '2006-05-04 19:05') AS 时间