RAND not different for every row in T-SQL UPDATE

Question

I have the follow T-SQL to update a table with test data:

UPDATE
SomeTable
SET
    Created = GETDATE ( ) - CAST ( RAND ( ) * 365 AS int ) ,
    LastUpdated = GETDATE ( ) - CAST ( RAND ( ) * 365 AS int )

I want it to pick random daes in the past year, unfortunately it uses the same date for every row. what is the best way to get it to be random every row it updates?

score 41 · Accepted Answer · answered Jul 31 '09 at 20:39

41

Use RAND(CHECKSUM(NEWID()))

NEWID returns a GUID
CHECKSUM makes it int, randomly
The int seeds the RAND

In your case, you could modulo the checkum because CHECKSUM(NEWID()) is already random.

CHECKSUM(NEWID()) % 365

answered Jul 31 '09 at 20:39

gbn

422,506
82
585
676

4

Great stuff. I wonder why they made it not vary per row, but that question is for another day :) – Keith Adler Jul 31 '09 at 20:58
3

AS an aside, your method does have one issue: it brings back negative values. Easy fix though: ABS( CHECKSUM(NEWID()) % 365 ) – Keith Adler Jul 31 '09 at 21:10
Great solution. And just shift and multiply by whatever you want to get an int within a range. – AFract Jun 14 '16 at 13:18

score 3 · Answer 2 · answered Jul 31 '09 at 21:01

3

if you only want days from the past year use this (based on @gbn's answer):

select GETDATE ( ) - ABS( CHECKSUM(NEWID()) % 365 )

answered Jul 31 '09 at 21:01

KM.

101,727
34
178
212

RAND not different for every row in T-SQL UPDATE

2 Answers2

Linked