How to update rows with a random date
SqlSql ServerSql Problem Overview
I have a simple SQL table which has a DateTime column. I would like to update all the rows (>100000 rows) with a random date. Is there a simple way to do this a SQL Query?
Sql Solutions
Solution 1 - Sql
Use this to generate a smalldatetime between 01 Jan 1900 and 06 Jun 2079 (not checked, SQL not installed)
DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
NEWID is better then trying to use RAND: RAND does not generate different values row in a single SELECT or UPDATE (well it didn't in SQL 2000, in case behaviour has changed).
Edit: like this
UPDATE
table
SET
datetimecol = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
Edit: changed 65535 to 65530 and added ABS to avoid overflow at upper limit of range
Solution 2 - Sql
I will complement the answers below,
SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
FROM your_table
This generates dates starting from 2000-01-01, and you can change the amount of days in the modulus value, I put 3650 (about 10 years), this approach doesn't 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
Solution 3 - Sql
This question seems quite old but my answer might be usefull 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 a given range.
Solution 4 - Sql
I adapted Jhonny's answer above to get dates from 10 years in the past:
SELECT dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate())
Note that this is SQLServer only.
Solution 5 - Sql
I used this to set a date of birth between 1940 and 1985 for all of my test data
SET [Birth Date] = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 16250), '1940-1-1 00:00:00.001')
Solution 6 - Sql
The following code will fill the StartDate column of the FiscalYear table with random dates between two given dates:
-- First, let's 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)
)
)
Solution 7 - Sql
I was looking for a question similar to this that also generated a 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/
Solution 8 - Sql
Using the code below you can get a random integer between @Min (1) and @Max (365), then using the dateadd funection you can create random dates in 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())
Solution 9 - Sql
you can try getting a random number (positive or negative) then adding that number to a date (possibly system date).
For example (I don't have access to sqlserver right now so I could not verify syntax)
DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1 - FLOOR(RAND(CAST(NEWID() AS binary(4))) * 365.25 * 90), 0)
Solution 10 - Sql
I combined several answers for myself, I think it it work for you. It took 40 seconds for me to execute this for 140k rows. i5, 1333MHZ, standart laptop hdd
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;