views:

161

answers:

2

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?

+5  A: 

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
gbn
Great stuff. I wonder why they made it not vary per row, but that question is for another day :)
Nissan Fan
AS an aside, your method does have one issue: it brings back negative values. Easy fix though: ABS( CHECKSUM(NEWID()) % 365 )
Nissan Fan
doh! thanks. I always forget this myself too...
gbn
+1  A: 

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

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