Random date between two given dates SQL Server 2012

Question

I am trying to follow one of the answers given in

How to insert 1000 random dates between a given range?

and used the following code to create a function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION RandomDate (@fromDate DATE, @toDate DATE) 
RETURNS DATE
AS 
BEGIN
   declare @days_between int
   declare @days_rand int

   set @days_between = datediff(day,@fromDate,@toDate)
   set @days_rand  = cast(RAND()*10000 as int)  % @days_between

   return dateadd(day, @days_rand, @fromDate)
end

When I try to create the function, I get the following error:

Msg 443, Level 16, State 1, Procedure RandomDate, Line 7
Invalid use of a side-effecting operator 'rand' within a function.

Any ideas on how to fix it?

Thanks

RAND() function is directly not allowed to use in the UDF. http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/ — Shiju Shaji, Feb 01 '16 at 06:12
Must it be a UDF? can't you use a stored procedure for that? — Zohar Peled, Feb 01 '16 at 07:06

score 0 · Answer 1 · answered Feb 01 '16 at 06:28

Create a view using the rand function

  CREATE VIEW randomView
    AS
    SELECT RAND() randomResult
    GO

Use the view in the function created.

 create function RandomDate ( @fromDate date, @toDate date) returns date AS 
BEGIN
 declare @days_between int
 declare @days_rand int

  declare @rndResult int

 select @rndResult=randomResultfrom randomView

 set @days_between = datediff(day,@fromDate,@toDate)
 set @days_rand  = cast(@rndResult*10000 as int)  % @days_between

 return dateadd( day, @days_rand, @fromDate )
end

could it be a function that would calls sp? for a reason, I do not want to use views. — SJ Johnson, Feb 01 '16 at 14:16

Random date between two given dates SQL Server 2012

1 Answers1