How to generate random dates in Oracle
I currently work as a software tester, and to fully test the application I had a need to insert random dates between 2006-12-01 and 2006-12-20 into an Oracle database. DBMS_RANDOM can be used to generate random numbers, and it can also be used to generate random dates with a few tricks.
Oracle supports the use of Julian dates, or actually Julian day number, which is the number of days that has elapsed since January 1, 4712 BC. This is an integer value (the Julian day number). Since DBMS_RANDOM generates a random value (number), and the number can be converted to a date, it is possible to generate random dates using DBMS_RANDOM. This is how to do it:
SELECT TO_CHAR(TO_DATE('2006-12-01', 'YYYY-MM-DD'), 'J') FROM DUAL;
This will generate the number 2454071. This is the Julian day number. Now, to generate a random date between 2006-12-01 and 2006-12-20 (that is a difference of 20 days), the following SQL query can be used.
The generated (and random) date can now easily be insterted into the table, or update an existing row.
Resources
DBMS_RANDOM
Orcale Database SQL Reference, Use of Julian Dates
Orcale Database SQL Reference, Selecting from the DUAL Table
Published 2007-01-01 21:00 GMT+0100 by Kristofer Gafvert