CODEWITHSUNDEEP
sql-server
kentor
kentor

Reputation: 18504

SQL Server : random date in specific range (including random hours, minutes,...)

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.

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.

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)

Upvotes: 9

Views: 15047

Answers (3)

Venu chembu
Venu chembu

Reputation: 21

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)

Upvotes: 2

Dudi Konfino
Dudi Konfino

Reputation: 1136

SELECT dateaddDATEADD(second, 
          second,  (rand()*60+1), 
            DATEADD(minute,
                    (rand()*60+1) , 
                    DATEADD(day, 
                            (rand()*365+1), 
                            DATEADD(year, 
                                    -1, 
                                    getdate()))) )

Upvotes: 0

DavidG
DavidG

Reputation: 118937

This is how I'd do it:

  1. Work out the number of seconds between from and to
  2. Get a random number between zero and the number of seconds
  3. Add that random number to the FromDate
  4. 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))

Upvotes: 9

Related Questions