sql server生成1000-9999之间的随机数或一个日期区间随机日期

==产生数字区间随机数==
--创建视图
create view myview as select re=rand()

--自定义函数:取得指定范围的随机数
create function mydata(
@a int,
@b int)
returns decimal(38,0)
as
begin
declare @r decimal(38,0)
select @r=cast(re*(@b-@a)+@a as decimal(38,0)) from myview
return(@r)
end
go

--调用(可以随意指定你要的数据范围)
select user_no,dbo.mydata(1000,9999) number from table1

--可以在你原来的查询基础上增加一列number，如不增加列,
--那就把上面的结果放入一个临时表 #a，然后update
--如：
update table1 set number1=a.number from #a a,table1 b where a.user_no=b.user_no


=========================================
==产生日期区间随机日期==
select cast(dbo.mydata(1993,1985) as nvarchar(4))+'-'+cast(dbo.mydata(12,1) as nvarchar(2))+'-'+cast(dbo.mydata(30,1) as nvarchar(2))

select dateadd(dd,datediff(dd,'2008-05-01','2008-06-30')*RAND(),'2008-05-01')
2008-05-01换为起始日期
2008-06-30换为结束日期

转载于:https://www.cnblogs.com/craig/archive/2008/06/16/1223360.html