社区首页 >问答首页 >如何在给定的范围内插入1000个随机日期？

问如何在给定的范围内插入1000个随机日期？
EN

Stack Overflow用户

提问于 2012-03-10 09:26:49

回答 4查看 43.8K关注 0票数 27

我是sql server的新手。我需要生成从给定日期范围中选择的随机日期。例如，雇员的就业日期应该介于2011-01-01和2011-12-31之间。生成的日期应该随机插入到1000行的表中。

有没有人可以指导我的问题？

sql

sql-server-2008

回答 4

Stack Overflow用户

发布于 2012-03-10 10:31:54

declare @FromDate date = '2011-01-01'
declare @ToDate date = '2011-12-31'

select dateadd(day, 
               rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)), 
               @FromDate)

票数 55

Stack Overflow用户

发布于 2018-04-27 18:16:58

您可以简单地使用此查询。

DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01')

如果要添加来自不同时间轴的日期，可以更改01-01-2011和364。364等于您要添加的天数。在本例中，它位于01-01-2011和31-12-2011之间。

(还包括31-12-2011。)

例如，假设您想在2018-01-01和2018-01-31之间添加一个随机日期，您可以像这样更改查询。

DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 31), '2018-01-01')

要插入(一行/日期)，只需使用以下命令...

DECLARE @rdate DATE = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 31), '2018-01-01')

INSERT INTO TableName ([DateColumn])
VALUES (@rdate);

输出

+-----+------------+
| ID  | DateColumn |
+-----+------------+
| 01  | 2018-01-21 |
+-----+------------+

在线演示：SQLFiddle.com

一次插入1000行...

DECLARE @rdate DATE
DECLARE @startLoopID INT = 1
DECLARE @endLoopID INT = 1000 -- Rows you want to add

WHILE @startLoopID <= @endLoopID
BEGIN
    SET @rdate = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01');
    SET @startLoopID = @startLoopID + 1;

    INSERT INTO TableName ([DateColumn])
    VALUES (@rdate);
END

输出

+--------+------------+
|  ID    | DateColumn |
+--------+------------+
| 10000  | 2010-04-07 |
| 10001  | 2010-07-29 |
| 10002  | 2010-11-18 |
| 10003  | 2010-05-27 |
| 10004  | 2010-01-31 |
| 10005  | 2010-08-26 |
|   ˅    |     ˅      |
| 20000  | 2010-06-26 |
+--------+------------+

在线演示：SQLFiddle.com

要更新现有行...

UPDATE TableName
SET [DateColumn] = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01')
WHERE condition;

在线演示：SQLFiddle.com

票数 19

Stack Overflow用户

发布于 2012-03-10 09:58:10

我给你写了这个简单的函数，它返回一个在日期范围内的随机日期：

create function date_rand ( @fromDate date, @toDate date) returns date
as
begin
 
 declare @days_between int
 declare @days_rand int

 set @days_between = datediff(day,@fromDate,@toDate)
 set @days_rand  = cast(RAND()*10000 as int)  % @days_between
 
 return dateadd( day, @days_rand, @fromDate )
end

调用函数的步骤：

select dbo.date_rand( '1/1/2001', '10/1/2001' )

您可以将函数与行生成器结合使用：

;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT dbo.date_rand( '1/1/2001', '10/1/2001' )
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 1000

编辑的

要生成随机数，请使用：

RAND(CHECKSUM(NEWID()))

而不是RAND()

编辑的II

函数返回“无效使用副作用运算符”“rand”of a Function“”错误。THis是因为我们不能使用像RAND()或NEWID()这样的非确定性函数。

一种解决方法是create a view like

create view myRandomNumber as 
select cast( RAND(CHECKSUM(NEWID()))*1000 as int) as new_rand

然后在函数中使用它：

...
select @days_rand  = new_rand  % @days_between from myRandomNumber
...

或者简单的，不使用函数，在select上写表达式。我只写了一个函数来一步一步的讲解。

declare @fromdate date
declare @todate date
set @fromdate = '1/1/2001'
set @todate = '10/1/2001'
;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT 
   dateadd( day, 
            cast( RAND(CHECKSUM(NEWID()))*1000 as int) % 
                         datediff(day,@fromDate,@toDate), 
            @fromDate )
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 1000