SQL random dates
Could you help me:
I have an arrival date column that needs 5000 rows adding random dates from 2010 to it.
For example: 01/01/2010
, 25/8/2010
, 03/05/2010
, 31/12/2010
, etc.
I think it will be an operator INSERT
, but I'm not sure.
thank
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
+3
source to share
1 answer
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 table of numbers or a 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
+5
source to share