把某列修改成随机时间

[dqk1985]

有表
A
2012-02-03
2012-06-09
2012-07-08


我想随机的把所有行修改成一个 2014-02-14 2014-02-28 之间的数据；

[-晴天]

create table tb(id int identity(1,1),t datetime)
insert into tb select '2012-12-23' union all select '2012-04-28' union all select '2012-06-08'
go
declare @i int,@j int
set @i=1
while @i<4
begin
	set @j=floor(15*rand()+1)
	update tb set t=dateadd(d,@j,'2014-02-13') where id=@i
	set @i=@i+1
end
go
select * from tb
/*
id          t
----------- -----------------------
1           2014-02-14 00:00:00.000
2           2014-02-24 00:00:00.000
3           2014-02-27 00:00:00.000

(3 行受影响)

*/
drop table tb

[guguda2008]

UPDATE TB SET A=DATEADD(DAY,ABS(CHECKSUM(NEWID()))%14,'2014-2-14')

[jorkin_me]

写个游标一行一行换。 rand()太坑人了。

[jorkin_me]

引用 6 楼 guguda2008 的回复:

UPDATE TB SET A=DATEADD(DAY,ABS(CHECKSUM(NEWID()))%14,'2014-2-14')

领教了，原来CHECKSUM可以这样用，非常感谢。

[唐诗三百首]

create table 表A(id int,x varchar(20))

insert into 表A(id,x)
 select 1,'2012-02-03' union all
 select 2,'2012-06-09' union all
 select 3,'2012-07-08'


declare @id int
declare ap scroll cursor for
 select id from 表A

open ap
fetch first from ap into @id
while(@@fetch_status<>-1)
begin
 update 表A 
 set x=(select top 1 convert(varchar,dateadd(d,number,'2014-02-14'),23)
        from master.dbo.spt_values 
        where type='P' and number<datediff(d,'2014-02-14','2014-02-28')
        order by newid())
 where id=@id       

 fetch next from ap into @id 
end

close ap
deallocate ap


-- 结果
select id,x from 表A

/*
id          x
----------- --------------------
1           2014-02-25
2           2014-02-20
3           2014-02-15

(3 row(s) affected)
*/

[dqk1985]

没有人吗？？？？

[LongRui888]

引用 1 楼 dqk1985 的回复:

没有人吗？？？？

估计都吃饭去了哈

[LongRui888]

把表中的所有记录都要随机修改，这个好像比较困难。

[LongRui888]

通过循环来update，适合数据量不是很大的：

--drop table a

create table A(v datetime)

insert into A
select '2012-02-03' union all
select '2012-06-09' union all
select '2012-07-08'
go


--通过循环来update
declare @i int
declare @s int

set @i = 1
set @s = (select COUNT(*) from a)

while @i <= @s
begin

	;with t
	as
	(
	select *,ROW_NUMBER() over(order by getdate()) rownum
	from a
	)

	update t
	set v = dateadd(day,(select top 1 number from master..spt_values 
						 where type = 'p' and number <=datediff(DAY,'2014-02-14','2014-02-28')+1 
						 order by checksum(NEWID())
						 ),
						 '2014-02-14')
	where rownum = @i

	set @i = @i + 1
end



select *
from a
/*
v
2014-03-01 00:00:00.000
2014-02-22 00:00:00.000
2014-02-27 00:00:00.000
*/

[lzw_0736]

引用 6 楼 guguda2008 的回复:

UPDATE TB SET A=DATEADD(DAY,ABS(CHECKSUM(NEWID()))%14,'2014-2-14')

%15