mercredi 6 mai 2015

Create random function that return DateTime in range : Invalid use of a side-effecting operator

Consider the SQL Server code :

CREATE FUNCTION dbo.CreateRandomDateTime ()
RETURNS DateTime
AS BEGIN
    DECLARE @DateFrom DATETime = '2012-01-01'
    DECLARE @DateTo DATeTime = '2012-06-30'
    DECLARE @DaysRandom Int= 0
    DECLARE @MillisRandom Int=0
    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, @DateTo)
    SELECT @DateTo = DATEADD(MILLISECOND, @MillisRandom, @DateTo)
    RETURN @DateTo
END

I want to create a function that returns a random DateTime , but when I execute the code I get :

Msg 443, Level 16, State 1, Procedure CreateRandomDateTime, Line 9
Invalid use of a side-effecting operator 'rand' within a function.
Msg 443, Level 16, State 1, Procedure CreateRandomDateTime, Line 12
Invalid use of a side-effecting operator 'rand' within a function.
Msg 8116, Level 16, State 1, Procedure CreateRandomDateTime, Line 14
Argument data type datetime is invalid for argument 2 of dateadd function.
Msg 8116, Level 16, State 1, Procedure CreateRandomDateTime, Line 15
Argument data type datetime is invalid for argument 2 of dateadd function.

But when I run the code without the body function , I get a random datetime , so it seems that I did something wrong with the function .

Where did I go wrong ?

Aucun commentaire:

Enregistrer un commentaire