如何插入指定时间段内的随机时间呢？
insert into T_user(logname,sub_date)
select myname,@mydate from test
@mydate 这个日期要在一个给定时间段内的随机日期值，如：2007-6-1至getdate(),
只要在这时间段内不重复即可.上面的sql语句只是我随便写的,用来描述问题，即在一张表内要插入一个结果集，但日期一列是指定一个时间段内的随机日期.
[解决办法]
declare @Date_start datetime
declare @Date_end datetime
set @Date_start= '2006-06-01 '
set @Date_end=getdate()
select 时间=dateadd(minute,abs(checksum(newid()))%(datediff(minute,@Date_start,@Date_end)+1),@Date_start)
into t
select * from t
drop table t
[解决办法]
Create Table test(myname Varchar(10))
Insert test Select 'A '
Create Table T_user(logname Varchar(10),sub_date DateTime)
GO
Declare @I Int, @mydate DateTime
Select @I = DateDiff(dd, '2007-06-01 ', GetDate())
Select @mydate = DateAdd(dd, Convert(Int, Rand(CheckSUM(NewID())) * @I), '2007-06-01 ')
insert into T_user(logname,sub_date)
select myname,@mydate from test
Select @mydate = DateAdd(dd, Convert(Int, Rand(CheckSUM(NewID())) * @I), '2007-06-01 ')
insert into T_user(logname,sub_date)
select myname,@mydate from test
Select * From T_user
GO
Drop Table T_user, test
--Result
/*
lognamesub_date
A2007-06-17 00:00:00.000
A2007-06-26 00:00:00.000
*/
[解决办法]
不一样？
declare @a table(logname varchar(20), sub_date smalldatetime)
declare @test table(logname varchar(20))
insert @test select 'Lee '
union all select 'Mr.Z '
union all select 'Jack '
declare @s datetime,@e datetime, @i int
set @s= '2007-01-01 '
set @e=getdate()
set @i=datediff(day,@s,@e)
insert into @a(logname,sub_date) select logname,dateadd(day,rand(checksum(newid()))*@i,@s) from @test
select * from @a
[解决办法]
我又改进了下，直接update用这个也可以了
update a1 set d = dateadd(d,convert(int,RAND(left(replace(replace(replace(replace(replace(replace(left(newid(),5), 'a ', ' '), 'b ', ' '), 'c ', ' '), 'd ', ' '), 'e ', ' '), 'f ', ' '),5))*datediff(dd,getdate(), '2007-6-1 ')),getdate())
