Saturday, October 16, 2010

T-SQL Random Date


I wrote this code to generate a random date between two dates. I wanted a random date for each row in a table. Use @beginDate and @endDate to specify the lower and upper bounds of the random date. The only tricky part is the checksum(newid()). You can try using the rand function without it, but you'll just get the same random date for every row in the table.

declare @beginDate datetime, @endDate datetime
set @beginDate = '1/1/2000'
set @endDate = getdate()

select dateadd(d, cast(-1 * datediff(d, @beginDate, @endDate)
* rand(checksum(newid())) as int), @endDate)
from sys.objects


If you want to update a date column with a random date, you can just do this:

update Orders
set orderdate = dateadd(d, cast(-1 * datediff(d, @beginDate, @endDate)
* rand(checksum(newid())) as int), @endDate)

No comments:

Post a Comment