CODEWITHSUNDEEP
sql-serverdatedatetimesql-server-2005
Bahaa Eldin
Bahaa Eldin

Reputation: 13

update multiple rows with random datetime sql server

I have table sql server 2005 that contains 3000 rows Like that

<<ID>>             <<Time>>
5620298     2015-01-05 16:00:00.000 
5620299     2015-01-06 16:00:00.000 
5620300     2015-01-07 16:00:00.000 
5620301     2015-01-08 16:00:00.000 
5620302     2015-01-09 16:00:00.000 
5620303     2015-01-10 16:00:00.000 
5620304     2015-01-11 16:00:00.000 
5620305     2015-01-12 16:00:00.000 
5620306     2015-01-13 16:00:00.000

i wanna to change time with random minutes between 1 to 10 minutes 

<<ID>>             <<Time>>
5620298     2015-01-05 16:02:00.000 
5620299     2015-01-06 16:05:00.000 
5620300     2015-01-07 16:01:00.000 
5620301     2015-01-08 16:00:00.000 
5620302     2015-01-09 16:02:00.000 
5620303     2015-01-10 16:07:00.000 
5620304     2015-01-11 16:06:00.000 
5620305     2015-01-12 16:09:00.000 
5620306     2015-01-13 16:00:00.000

How can I do this?

Thanks!

Upvotes: 1

Views: 483

Answers (2)

muffaddal shakir
muffaddal shakir

Reputation: 81

You can try RAND()

UPDATE TBL SET [TIME] = DATEADD(MINUTE, ABS(ROUND(RAND()*10,0)) , [TIME])

You may get duplicate random numbers, agree with Ale; we can use NEWID() instead.

Upvotes: 0

Alex K.
Alex K.

Reputation: 175866

You could:

UPDATE TBL
    SET [TIME] = DATEADD(MINUTE, ABS(CHECKSUM(NEWID()) % 10) + 1, [TIME])

Not very efficient but presumably this is just for testing.

Upvotes: 2

Related Questions