RAND not different for every row in T-SQL UPDATE

问题:

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?

回答1:

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

回答2:

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

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