3

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
wafw1971
  • 361
  • 3
  • 7
  • 17
  • [Related](http://stackoverflow.com/questions/5519486/select-a-random-date-within-specific-range), [also related](http://stackoverflow.com/questions/794637/how-to-update-rows-with-a-random-date). – Bernhard Barker Feb 14 '13 at 15:29

1 Answers1

5
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
PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • Excellent, love it. One update though: *SET @counter = 0* I think is what you meant. – Kprof Feb 14 '13 at 15:35
  • Your second example will generate the same value for every row - `RAND()` only gets called once. – Damien_The_Unbeliever Feb 14 '13 at 15:35
  • @Damien_The_Unbeliever, yes I realized this and modified based on 'related' post in comments – PinnyM Feb 14 '13 at 15:36
  • Hi Pinny it doesn't seem to be working I am getting the following error message, See Script above. Msg 257, Level 16, State 3, Line 16 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. – wafw1971 Feb 14 '13 at 15:45
  • Hi Pinny, my fault in the Create table section I put the Arrival date as int not date. Thanks for your help. – wafw1971 Feb 14 '13 at 15:48