Randomise Dates SQL
date, sql, sql-server, t-sql
Solution
DECLARE @counter int
SET @counter = 0
WHILE @counter < 5000
BEGIN
INSERT INTO myTable (arrivalDate)
SELECT DATEADD(day, CAST(RAND() * 365 as int), '2010-1-1')
SET @counter = @counter + 1
END
And if you have a numbers table or generator, you can avoid the loop:
INSERT INTO myTable (arrivalDate)
SELECT DATEADD(day, ABS(CHECKSUM(NEWID()) % 365), '2010-1-1')
FROM ( GenerateRowNumbers(5000, 1) ) t
Problem
Can you please help: I have an arrival date column that needs 5000 rows adding to it with random dates from the year 2010. For example: `01/01/2010`, `25/8/2010`, `03/05/2010`, `31/12/2010`, etc I think it would be an `INSERT` statement but I am unsure. Thanks Wayne ``` USE Occupancy CREATE TABLE Book (BookNumber varchar(30), ArrivalDate int) DECLARE @BookNumber varchar(30) DECLARE @ArrivalDate int SET @BookNumber = 1 SET @ArrivalDate = 0 WHILE @BookNumber <= 5000 WHILE @ArrivalDate <= 5000 BEGIN INSERT INTO Book (BookNumber,ArrivalDate) SELECT 'B' + Right ('00000000' + CAST(@BookNumber AS varchar(30)),8), DATEADD(day, CAST(RAND() * 365 as int), '2010-1-1') SET @BookNumber = @BookNumber + 1 SET @ArrivalDate = @ArrivalDate + 1 END ```