How to update strings with random date

Question

I have a simple SQL table that has a DateTime column in it. I would like to update all rows (> 100000 rows) with a random date. Is there an easy way to do this SQL query?

Answer 1

Use this to generate smalldatetime between January 01, 1900 and June 06, 2079 (not verified, SQL not installed)

DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0) 

NEWID is better than trying to use RAND: RAND does not generate a row with different values ​​in the same SELECT or UPDATE (well, this did not happen in SQL 2000, if the behavior changed).

Edit: e.g.

 UPDATE table SET datetimecol = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0) 

Edit: changed from 65535 to 65530 and added ABS to avoid overflow at the upper limit of the range

Answer 2

I will supplement the answers below,

 SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01') FROM your_table 

This creates dates starting from 2000-01-01, and you can change the number of days in the module value, I set 3650 (about 10 years), this approach does not overflow.

If you want to update, then

 UPDATE your_table SET your_date_field = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01') WHERE your_conditions 

Answer 3

This question seems pretty old, but my answer might be useful to others.

  Update table SET Time= DateAdd(d, ROUND(DateDiff(d, '2010-01-01', '2013-12-31') * RAND(CHECKSUM(NEWID())), 0), DATEADD(second,CHECKSUM(NEWID())%48000, '2010-01-01')) 

This generates a random datetime between the given range.

Answer 4

I adapted Jhonny to answer above to get dates from 10 years in the past:

 SELECT dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate()) 

Please note that this is only SQLServer.

Answer 5

The following code will populate the StartDate column of the FiscalYear table with random dates between two dates:

 -- First, let declare the date range. DECLARE @date_from DATETIME; DECLARE @date_to DATETIME; -- Set the start and date dates. In this case, we are using -- the month of october, 2006. SET @date_from = '1985-10-14'; SET @date_to = '2009-04-27'; UPDATE FiscalYear SET StartDate = ( -- Remember, we want to add a random number to the -- start date. In SQL we can add days (as integers) -- to a date to increase the actually date/time -- object value. @date_from + ( -- This will force our random number to be >= 0. ABS ( -- This will give us a HUGE random number that -- might be negative or positive. CAST(CAST(NewID() AS BINARY(8)) AS INT) ) -- Our random number might be HUGE. We can't have -- exceed the date range that we are given. -- Therefore, we have to take the modulus of the -- date range difference. This will give us between -- zero and one less than the date range. % -- To get the number of days in the date range, we -- can simply substrate the start date from the -- end date. At this point though, we have to cast -- to INT as SQL will not make any automatic -- conversions for us. CAST((@date_to - @date_from) AS INT) ) ) 

Answer 6

I used this to set a date of birth between 1940 and 1985 for all my test data.

 SET [Birth Date] = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 16250), '1940-1-1 00:00:00.001') 

Answer 7

I was looking for a question like this that also generated random time, and I found this script. Thought it might be useful here:

 DECLARE @DateFrom DATETime = '2001-01-01' DECLARE @DateTo DATeTime = '2013-11-30' DECLARE @DaysRandom Int= 0 DECLARE @MillisRandom Int=0 --get random number of days select @DaysRandom= DATEDIFF(day,@DateFrom,@DateTo) SELECT @DaysRandom = ROUND(((@DaysRandom -1) * RAND()), 0) --get random millis SELECT @MillisRandom = ROUND(((99999999) * RAND()), 0) SELECT @DateTo = DATEADD(day, @DaysRandom, @DateFrom) SELECT @DateTo = DATEADD(MILLISECOND, @MillisRandom, @DateTo) SELECT @DateTo 

I got it from here: http://crodrigues.com/sql-server-generate-random-datetime-within-a-range/

Answer 8

Using the code below, you can get a random integer between @Min (1) and @Max (365), and then using the dateadd function, you can create random dates for the last year.

 CREATE VIEW vRandNumber AS SELECT RAND() as RandNumber GO CREATE FUNCTION RandNumber(@Min int, @Max int) RETURNS int AS BEGIN RETURN round(@Min + (select RandNumber from vRandNumber) * (@Max-@Min),0) END GO Update table1 set theDate = dateadd(d,0-dbo.RandNumber(1,365),getdate()) 

Answer 9

you can try to get a random number (positive or negative) and then add that number to the date (possibly to the system date).

For example (I do not have access to sqlserver right now, so I could not check the syntax)

 DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1 - FLOOR(RAND(CAST(NEWID() AS binary(4))) * 365.25 * 90), 0) 

Answer 10

I have collected some answers for myself, I think this will work for you. It took me 40 seconds to complete this for 140k lines. i5, 1333MHZ, standard hdd laptop

  DECLARE @rank INT = 0; WHILE @rank < yourmaxrow --(you can use Select count (*) from your table name as well) BEGIN DECLARE @FromDate DATETIME = DATEADD(DAY, -720, GETDATE()) -- 2 years back DECLARE @ToDate DATETIME = DATEADD(DAY, -1, GETDATE()) -- until yesterday DECLARE @Seconds INT = DATEDIFF(SECOND, @FromDate, @ToDate) DECLARE @Random INT = ROUND(((@Seconds-1) * RAND()), 0) DECLARE @Milliseconds INT = ROUND((999 * RAND()), 0) update yourtablename Set yourdatetiemcolumnname = DATEADD(MILLISECOND, @Milliseconds, DATEADD(SECOND, @Random, @FromDate)) WHERE Id = @rank SET @rank = @rank + 1; END;