SQL 如何使用随机日期更新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/794637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update rows with a random date
提问by Martin
I have a simple SQL table which has a DateTime column. I would like to update all the rows (>100000 rows) with a random date. Is there a simple way to do this a SQL Query?
我有一个简单的 SQL 表，它有一个 DateTime 列。我想用随机日期更新所有行（> 100000 行）。有没有一种简单的方法来执行 SQL 查询？
回答by gbn
Use this to generate a smalldatetime between 01 Jan 1900 and 06 Jun 2079 (not checked, SQL not installed)
使用它生成 1900 年 1 月 1 日至 2079 年 6 月 6 日之间的 smalldatetime（未选中，未安装 SQL）
DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
NEWID is better then trying to use RAND: RAND does not generate different values row in a single SELECT or UPDATE (well it didn't in SQL 2000, in case behaviour has changed).
NEWID 比尝试使用 RAND 更好：RAND 不会在单个 SELECT 或 UPDATE 中生成不同的值行（在 SQL 2000 中不会，以防行为发生变化）。
Edit: like this
编辑：像这样
UPDATE
table
SET
datetimecol = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
Edit: changed 65535 to 65530 and added ABS to avoid overflow at upper limit of range
编辑：将 65535 更改为 65530 并添加 ABS 以避免在范围上限溢出
回答by Jhonny D. Cano -Leftware-
I will complement the answers below,
我将补充下面的答案，
SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
FROM your_table
This generates dates starting from 2000-01-01, and you can change the amount of days in the modulus value, I put 3650 (about 10 years), this approach doesn't overflow.
这将生成从 2000-01-01 开始的日期，您可以更改模数值中的天数，我输入 3650（大约 10 年），这种方法不会溢出。
If you want to update, then
如果你想更新，那么
UPDATE your_table
SET your_date_field = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
WHERE your_conditions
回答by Pieter_Daems
This question seems quite old but my answer might be usefull to others.
这个问题似乎很老，但我的回答可能对其他人有用。
Update table
SET Time= DateAdd(d, ROUND(DateDiff(d, '2010-01-01', '2013-12-31') * RAND(CHECKSUM(NEWID())), 0),
DATEADD(second,CHECKSUM(NEWID())%48000, '2010-01-01'))
This generates a random datetime between a given range.
这会在给定范围之间生成随机日期时间。
回答by Christopher Marshall
I adapted Jhonny's answer above to get dates from 10 years in the past:
我改编了上面 Jhonny 的答案，以获取过去 10 年的日期：
SELECT dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate())
Note that this is SQLServer only.
请注意，这仅适用于 SQLServer。
回答by CSharper
The following code will fill the StartDate column of the FiscalYear table with random dates between two given dates:
以下代码将使用两个给定日期之间的随机日期填充 FiscalYear 表的 StartDate 列：
-- First, let's declare the date range.
DECLARE @date_from DATETIME;
DECLARE @date_to DATETIME;
-- Set the start and date dates. In this case, we are using
-- the month of october, 2006.
SET @date_from = '1985-10-14';
SET @date_to = '2009-04-27';
UPDATE FiscalYear SET StartDate =
(
-- Remember, we want to add a random number to the
-- start date. In SQL we can add days (as integers)
-- to a date to increase the actually date/time
-- object value.
@date_from +
(
-- This will force our random number to be >= 0.
ABS
(
-- This will give us a HUGE random number that
-- might be negative or positive.
CAST(CAST(NewID() AS BINARY(8)) AS INT)
)
-- Our random number might be HUGE. We can't have
-- exceed the date range that we are given.
-- Therefore, we have to take the modulus of the
-- date range difference. This will give us between
-- zero and one less than the date range.
%
-- To get the number of days in the date range, we
-- can simply substrate the start date from the
-- end date. At this point though, we have to cast
-- to INT as SQL will not make any automatic
-- conversions for us.
CAST((@date_to - @date_from) AS INT)
)
)
回答by Paul Cunningham
I used this to set a date of birth between 1940 and 1985 for all of my test data
我用它为我所有的测试数据设置了 1940 年到 1985 年之间的出生日期
SET [Birth Date] = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 16250), '1940-1-1 00:00:00.001')
回答by aug
I was looking for a question similar to this that also generated a random time and I found this script. Thought it might be useful here:
我正在寻找一个与此类似的问题，它也生成了一个随机时间，我找到了这个脚本。认为它可能在这里有用：
DECLARE @DateFrom DATETime = '2001-01-01'
DECLARE @DateTo DATeTime = '2013-11-30'
DECLARE @DaysRandom Int= 0
DECLARE @MillisRandom Int=0
--get random number of days
select @DaysRandom= DATEDIFF(day,@DateFrom,@DateTo)
SELECT @DaysRandom = ROUND(((@DaysRandom -1) * RAND()), 0)
--get random millis
SELECT @MillisRandom = ROUND(((99999999) * RAND()), 0)
SELECT @DateTo = DATEADD(day, @DaysRandom, @DateFrom)
SELECT @DateTo = DATEADD(MILLISECOND, @MillisRandom, @DateTo)
SELECT @DateTo
I got it from here: http://crodrigues.com/sql-server-generate-random-datetime-within-a-range/
我从这里得到它：http: //crodrigues.com/sql-server-generate-random-datetime-within-a-range/
回答by northpole
you can try getting a random number (positive or negative) then adding that number to a date (possibly system date).
您可以尝试获取一个随机数（正数或负数），然后将该数字添加到日期（可能是系统日期）。
For example (I don't have access to sqlserver right now so I could not verify syntax)
例如（我现在无法访问 sqlserver，所以我无法验证语法）
DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1 - FLOOR(RAND(CAST(NEWID() AS binary(4))) * 365.25 * 90), 0)
回答by Jon Masters
Using the code below you can get a random integer between @Min (1) and @Max (365), then using the dateadd funection you can create random dates in the last year.
使用下面的代码，您可以获得@Min (1) 和@Max (365) 之间的随机整数，然后使用 dateadd 函数您可以创建去年的随机日期。
CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber
GO
CREATE FUNCTION RandNumber(@Min int, @Max int)
RETURNS int
AS
BEGIN
RETURN round(@Min + (select RandNumber from vRandNumber) * (@Max-@Min),0)
END
GO
Update table1
set theDate = dateadd(d,0-dbo.RandNumber(1,365),getdate())
回答by MEO
I combined several answers for myself, I think it it work for you. It took 40 seconds for me to execute this for 140k rows. i5, 1333MHZ, standart laptop hdd
我为自己组合了几个答案，我认为它对你有用。我花了 40 秒为 140k 行执行此操作。i5, 1333MHZ, 标准笔记本硬盘
DECLARE @rank INT = 0;
WHILE @rank < yourmaxrow --(you can use Select count (*) from your table name as well)
BEGIN
DECLARE @FromDate DATETIME = DATEADD(DAY, -720, GETDATE()) -- 2 years back
DECLARE @ToDate DATETIME = DATEADD(DAY, -1, GETDATE()) -- until yesterday
DECLARE @Seconds INT = DATEDIFF(SECOND, @FromDate, @ToDate)
DECLARE @Random INT = ROUND(((@Seconds-1) * RAND()), 0)
DECLARE @Milliseconds INT = ROUND((999 * RAND()), 0)
update yourtablename
Set yourdatetiemcolumnname = DATEADD(MILLISECOND, @Milliseconds, DATEADD(SECOND, @Random, @FromDate))
WHERE Id = @rank
SET @rank = @rank + 1;
END;
