CODEWITHSUNDEEP
t-sqlrandom
blint
blint

Reputation: 191

randomly add between 1 and 180 minutes to an existing datetime value in SQL Server

We have a large table with two datetime columns, starttime and endtime. endtime is not populated. starttime has data. We would like to populate endtime like this:

         update t
         set endtime = DateAdd(mi, some random int between 1 and 180, starttime)

Can that be done in T-SQL, so that a varying number of minutes is added, i.e. all the rows don't have the same number of minutes added to the starttime?

Upvotes: 4

Views: 2380

Answers (2)

Amali
Amali

Reputation: 31

update t
set endtime = DateAdd(mi, CAST( RAND()*1000 AS INT)  % 180, starttime)

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147304

You can generate a random number for each row by using a CHECKSUM / NEWID() approach:

UPDATE t
SET endtime = DATEADD(mi, ABS(CHECKSUM(NEWID())) % 180, starttime)

Upvotes: 7

Related Questions