Generate a random date in Oracle with DBMS_RANDOM

Question

I have this anonymous block:

DECLARE
   V_DATA   DATE;
BEGIN
   V_DATA := '01-GEN-2000';

   HR.STATISTICHE.RATINGOPERATORI (V_DATA);
   COMMIT;
END;

but I would to generate the date in a random way. How can I do?

Unsound

A random date in what range? Is any valid date value acceptable so that a large fraction of the dates you generate will be B.C. and a relatively small fraction will be in the 20th or 21st century? Do you want the time component to be random as well or do you want the dates to be at midnight as in your example? – Edmonds

Right! For example a random date from 01-01-2000 and on. It's this possible? – Unsound

The time component could be random. No problem. I'm interested only on the date. – Unsound

OK. That answers the lower bound of the range. What is the upper bound of the range? Or do you want values up to December 31, 9999? – Edmonds

The upper boud isn't important – Unsound

Answer 1

You can generate random dates between two dates ,as displayed in the query below .Random Dates are generated between 1-jan-2000 and 31-dec-9999

  SELECT TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J')
                                    ,TO_CHAR(DATE '9999-12-31','J')
                                    )
                    ),'J'
               ) FROM DUAL;

OR you can use

SELECT TO_DATE (
              TRUNC (
                     DBMS_RANDOM.VALUE (2451545, 5373484) 
                    )
                , 'J'
              )
  FROM DUAL

In the above example ,the first value is 01-Jan-2000 and the second value id 31-dec-9999

Dwan

I obtain this error when I compile: ORA-01843: mese non valido ORA-06512: a line 4. This means that month is not valid. – Unsound

+1.. I did edit the first example to explicitly specify the date format mask though, which will allow it to work regardless of NLS setting. I imagine that is why @Unsound was getting the ORA-01843. – Stalder

I replaced TO_DATE with ANSI date literals. It's shorter and avoids lots of potential issues with TO_DATE. – Hustle

Answer 2

To generate random date you can use

select to_date('2010-01-01', 'yyyy-mm-dd')+trunc(dbms_random.value(1,1000)) from dual

or for random datetime

select to_date('2010-01-01', 'yyyy-mm-dd')+dbms_random.value(1,1000) from dual

Seasonseasonable

Answer 3

If you want to see it's logic, you can also use this code.

  create or replace procedure genDate(result out nvarchar2) IS
  year  number;
  month  number;
  day  number;
Begin
  year:=FLOOR(DBMS_RANDOM.value(2000,2100));
  month:=FLOOR(DBMS_RANDOM.value(1,12));
  IF month=2 and (year/4)=0 and (year/100)!=0 then
    day:=FLOOR(DBMS_RANDOM.value(1,29));
  ELSIF month=2 or (year/100)=0 then
    day:=FLOOR(DBMS_RANDOM.value(1,28));
  ELSIF MOD(month,2)=1 then
    day:=FLOOR(DBMS_RANDOM.value(1,31));
  ELSIF MOD(month,2)=0 and month!=2 then
    day:=FLOOR(DBMS_RANDOM.value(1,30));
  END IF;  
  result:=month||'-'||day||'-'||year;
End;

Fults

Answer 4

here is one more option to generate date going back from now where 365 - days quanitity to move back from today, 'DD.MM.YYYY'- mask

to_char(sysdate-dbms_random.value()*365, 'DD.MM.YYYY')

Arioso

Answer 5

I needed to generate employee data for testing. Each employee needed a date of birth that put them between 16 and 65 years of age, and a date of hire sometime between their 16th birthday and SYSDATE. Here's how...

FUNCTION randomDateInRange(alpha IN DATE, omega IN DATE) RETURN DATE IS
BEGIN
    RETURN alpha + DBMS_RANDOM.VALUE(0, omega - alpha);
END;

...and then, to use this function...

-- an employee can be any age from 16 to 65 years of age
DoB := randomDateInRange(
    SYSDATE - INTERVAL '65' YEAR,
    SYSDATE - INTERVAL '16' YEAR
);

-- an employee could have been hired any date since their sixteenth birthday
DoH := randomDateInRange(
    DoB + INTERVAL '16' YEAR,
    SYSDATE
);

Kimball