I would like to create a random date for a SQL Server update query. I found a lot examples for random days or something similar but I couldn't find something which creates a random date with random date, hours, minutes, seconds AND milliseconds.
我想为SQL Server更新查询创建一个随机日期。我发现很多随机天或类似的例子,但我找不到创建随机日期,随机日期,小时,分钟,秒和毫秒的东西。
This is what I use to create the date randomly but it always gives me 00 as hour, minute, seconds and milliseconds and I don't know how I can randomize them as well.
这是我用来随机创建日期但它总是给我00作为小时,分钟,秒和毫秒,我不知道如何随机化它们。
This is my query:
这是我的查询:
declare @FromDate date = GETDATE()-2
declare @ToDate date = GETDATE()-1
UPDATE ACCOUNTS
SET dateFinished=
dateadd(day, rand(checksum(newid())) * (1 + datediff(day, @FromDate, @ToDate)), @FromDate)
3 个解决方案
#1
8
This is how I'd do it:
我就是这样做的:
- Work out the number of seconds between from and to
- 计算from和to之间的秒数
- Get a random number between zero and the number of seconds
- 获取零到秒数之间的随机数
- Add that random number to the FromDate
- 将该随机数添加到FromDate
- Finally randomise the number of milliseconds
- 最后随机化毫秒数
DECLARE @FromDate DATETIME = DATEADD(DAY, -2, GETDATE())
DECLARE @ToDate DATETIME = DATEADD(DAY, -1, GETDATE())
DECLARE @Seconds INT = DATEDIFF(SECOND, @FromDate, @ToDate)
DECLARE @Random INT = ROUND(((@Seconds-1) * RAND()), 0)
DECLARE @Milliseconds INT = ROUND((999 * RAND()), 0)
SELECT DATEADD(MILLISECOND, @Milliseconds, DATEADD(SECOND, @Random, @FromDate))
#2
0
SELECT dateaddDATEADD(second,
second, (rand()*60+1),
DATEADD(minute,
(rand()*60+1) ,
DATEADD(day,
(rand()*365+1),
DATEADD(year,
-1,
getdate()))) )
#3
0
declare @FromDate dateTIME = '2014-01-01'
declare @ToDate dateTIME = '2014-12-31'
select top 100 dateadd(day,rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)), @FromDate) FROM Tabled(give your table name)
#1
8
This is how I'd do it:
我就是这样做的:
- Work out the number of seconds between from and to
- 计算from和to之间的秒数
- Get a random number between zero and the number of seconds
- 获取零到秒数之间的随机数
- Add that random number to the FromDate
- 将该随机数添加到FromDate
- Finally randomise the number of milliseconds
- 最后随机化毫秒数
DECLARE @FromDate DATETIME = DATEADD(DAY, -2, GETDATE())
DECLARE @ToDate DATETIME = DATEADD(DAY, -1, GETDATE())
DECLARE @Seconds INT = DATEDIFF(SECOND, @FromDate, @ToDate)
DECLARE @Random INT = ROUND(((@Seconds-1) * RAND()), 0)
DECLARE @Milliseconds INT = ROUND((999 * RAND()), 0)
SELECT DATEADD(MILLISECOND, @Milliseconds, DATEADD(SECOND, @Random, @FromDate))
#2
0
SELECT dateaddDATEADD(second,
second, (rand()*60+1),
DATEADD(minute,
(rand()*60+1) ,
DATEADD(day,
(rand()*365+1),
DATEADD(year,
-1,
getdate()))) )
#3
0
declare @FromDate dateTIME = '2014-01-01'
declare @ToDate dateTIME = '2014-12-31'
select top 100 dateadd(day,rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)), @FromDate) FROM Tabled(give your table name)