How do I generate random date in specific range for each row in a TSQL Select?

Question

My code is like following How do I generate random date in specific range for each row in a TSQL Select

    Declare @id int;
    Declare @randomDate;
    Declare crs Cursor For
    Select id from myTable 
    open crs 
    fetch next from crs into @id
    while @@FETCH_STATUS=0
    begin
    SET @randomDate=''
    --Generate randomdate @randomDate
    update myTable set dateModified=@randomDate where id=@id;
    fetch next from crs into @id
    end
    close crs
    deallocate crs

You don't need a cursor, they are very slow, if you can't update the entire table in one go due to logging issues then use a while loop to update in batches. Avoid one row at a time updates. — pacreely, Jan 22 '17 at 15:41

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

As per following answer,

Update myTable
SET
 dateModified = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

You do not need any cursors to accomplish this. Cursors are bad for simple operations like this one.

edited May 23 '17 at 11:52

Community

1
1

answered Jan 21 '17 at 09:02

Atilla Ozgur

14,339
3
49
69

Thanks for your answer. I need specific range like between 2011/01/01 and 2016/12/12 – yunusdgn_tr Jan 21 '17 at 15:52

score 0 · Answer 2 · answered Jan 21 '17 at 09:10

You can use CTE to generate valid dates and select one of them randomly:

declare @fromDate date = cast('2016/12/12' as date),
        @toDate date = cast('2017/01/10' as date);

with cte([date]) as (
    select @fromDate
    union all
    select dateadd(day, 1, [date])
    from cte
    where [date] < @toDate
)
select top(1) *
from cte
order by newid();

score 0 · Answer 3 · answered Jan 21 '17 at 15:34

This will provide a random date between a specific start and end date. I've introduced the @gap variable for performance reasons, it removes the cost of the DATEDIFF function out of the main query.

DECLARE @start DATETIME = '20170101'
DECLARE @end DATETIME = '20170201'
DECLARE @gap INT = DATEDIFF(DD,@start,@end)

UPDATE mytable SET dateModified = DATEADD(DD,@gap*RAND(),@start)

How do I generate random date in specific range for each row in a TSQL Select?

3 Answers3