34,575
社区成员
发帖
与我相关
我的任务
分享
--测试数据
declare @t table (a1 varchar(10),a2 datetime)
insert @t select '200812' ,'2008-12-26 16:49:39.310'
union all select '200812' ,'2008-12-26 16:49:39.450'
union all select '200812' ,'2008-12-26 16:49:39.467'
union all select '200812' ,'2008-12-26 16:49:39.607'
union all select '200812' ,'2008-12-26 16:49:39.607'
union all select '200812' ,'2008-12-26 16:49:39.750'
union all select '200812' ,'2008-12-26 16:49:39.763'
union all select '200812' ,'2008-12-26 16:49:39.903'
union all select '200812' ,'2008-12-26 16:49:39.903'
union all select '200812' ,'2008-12-26 16:49:40.043'
--更新
declare @a datetime
update @t set @a=case when dateadd(day,0-cast(rand()*26 as int),isnull(@a,a2))<'2008-12-01'
then dateadd(day,0-cast(rand()*26 as int),a2)
else dateadd(day,0-cast(rand()*26 as int),isnull(@a,a2)) end,
@a=dateadd(hour,cast(rand()*8+8 as int),@a),
a2=case when datepart(weekday,@a) not in (1,7)
and datepart(hour,@a) not between 12 and 14 then @a else a2 end
--这个关键是产生随机时间
declare @daydate datetime,@day int,@hour int
declare @table table(id int identity(1,1),x datetime)
declare @m int,@mm int
set @m=1
while @m<30000
begin
select @day=cast(rand()*100000 as int)%25
select @hour=cast(rand()*20 as int)
select @daydate=dateadd(hour,@hour,dateadd(d,-@day,getdate()))
if(datediff(d,@daydate,getdate())<27 and datediff(d,@daydate,getdate())>1)
begin
if(datepart(weekday,@daydate)-1<>6 or datepart(weekday,@daydate)-1<>-1)
begin
if((datepart(hour,@daydate)>=8 and datepart(hour,@daydate)<12) or(datepart(hour,@daydate)>=14 and datepart(hour,@daydate)<16))
insert into @table select dateadd(ms,rand()*60,dateadd(s,rand()*600,@daydate))
end
end
set @m=@m+1
end
select * from @table
--
/*
--自己随便连接UPDATE下
*/