Oracle function to return random date and timestamp between VALUES-CodePudding

I'm having some difficulties creating a function that returns a random DATE( next is a function that returns a random timestamp) when passed in a from and to date range.

I am looking to have the value also include a random time associated with the DATE. For the timestamp function a random fractional value.

Below is what I have so far for the DATE function but I can't seem to get it to compile. Any help would be greatly appreciated. Thanks to all who answer.


CREATE OR REPLACE FUNCTION random_date(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN date DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
RETURN  p_from
         DBMS_RANDOM.VALUE(0, p_to -  p_from   1);
END random_date;
/

CodePudding user response：

It:

is missing the BEGIN keyword;
does not want it to be DETERMINISTIC (because it will not be); and
can be simpler:

CREATE FUNCTION random_date(
  p_from IN DATE,
  p_to   IN DATE
) RETURN DATE
IS
BEGIN
  RETURN p_from   DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_date;
/

And, for TIMESTAMPs:

CREATE FUNCTION random_timestamp(
  p_from IN TIMESTAMP,
  p_to   IN TIMESTAMP
) RETURN TIMESTAMP
IS
BEGIN
  RETURN p_from   DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_timestamp;
/