CODEWITHSUNDEEP
sql-servert-sql
yunusdgn_tr
yunusdgn_tr

Reputation: 1

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

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

Upvotes: 0

Views: 2600

Answers (3)

pacreely
pacreely

Reputation: 1931

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)

Upvotes: 0

shA.t
shA.t

Reputation: 16958

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();

Upvotes: 0

Atilla Ozgur
Atilla Ozgur

Reputation: 14701

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.

Upvotes: 1

Related Questions