How To Get Random Dates In Oracle PLSQL

Have you ever worked on a project in PL/SQL where you wanted to create random dates?

And even worse, what if the random dates need to be in a given date range?

I am working on a such project now, and in this little tutorial I will show you one way this can be done.

Background

Ok, since I am the creator of The oracle*tuts Learning Network, I want to have some more test data than just the SCOTT and HR sample schemas that come with Oracle. I need this for many of my samples, and tutorials.

What I did was actually to find a list up 100 actors on the Internet, and then tweaked their names a little bit. The thought is to create a customer table in my test schema.

I do not want to show their real birth dates, so I thought: “Why not just generate some random dates?”. So, this is what I threw together as one of the pieces in my little “project”.

I have used the DBMS_RANDOM package before to generate a random winner for one of the contests at my work, so that is the package we are going to use in this sample as well.

A “criteria” that I came up with was that I wanted my fictional people to be born any time between January 1940, and they should be at least 20 years old.

This is what I came up with.

A sample of using DBMS_RANDOM to get a random date in PL/SQL

Before I show the code, I just wanted to mention a few words about the Julian date format in Oracle. This is what I am going to use in the code below.

The Julian day format of the date is counted as the number of days since January 1, 4712 B.C. This is the earliest date supported by the Oracle database.

Ok, here we go, the code:

 

DECLARE
	--	---------------------------------------------------------
	--	Global variable declarations
	--	---------------------------------------------------------
	dgStartDate		DATE;
	dgEndDate		DATE;
	dgRandomDate	DATE;

	--	-------------------  ***** -------------------------------

	--	---------------------------------------------------------
	--	Function declarations
	--	---------------------------------------------------------

	FUNCTION getRandomDate(pStartDate IN DATE, pEndDate IN DATE) RETURN DATE
    IS
    	dRandomDate		DATE;
        piStartNumber	PLS_INTEGER;
        piEndNumber		PLS_INTEGER;
    BEGIN
    	-- 1. Convert the start date to Julian date numbers
        piStartNumber := TO_NUMBER(TO_CHAR(pStartDate, 'J'));
        piEndNumber := TO_NUMBER(TO_CHAR(pEndDate, 'J'));

        -- 2. Using the DBMS_RANDOM function to get the random date
		dRandomDate := TO_DATE(TRUNC(DBMS_RANDOM.VALUE(piStartNumber, piEndNumber)), 'J');

		-- Test output
        dbms_output.put_line(	'Random date between '
        						|| TO_CHAR(pStartDate, 'MM/DD/YYYY')
                                || ' and '
                                || TO_CHAR(pEndDate, 'MM/DD/YYYY')
                                || ' is: '
                                || TO_CHAR(dRandomDate, 'MM/DD/YYYY') );

    	RETURN dRandomDate;
    END;

--	---------------------------------------------------------
--	Main PL/SQL block
--	---------------------------------------------------------
BEGIN
	dgStartDate	:= TO_DATE('01/01/1940', 'MM/DD/YYYY');
	dgEndDate := SYSDATE - (365 * 20); -- About 20 years ago

	dgRandomDate := getRandomDate(dgStartDate, dgEndDate);

	-- Run it ten times to see that it shows different dates each time
	FOR i IN 1..10 LOOP
		dgRandomDate := getRandomDate(dgStartDate, dgEndDate);
	END LOOP;
END
;

The “core” in the code above is actually line # 26:

dRandomDate := TO_DATE(TRUNC(DBMS_RANDOM.VALUE(piStartNumber, piEndNumber)), 'J');

What happens here is that we use the VALUE function in the DBMS_RANDOM package. This function actually just understand numbers, so on line # 22, and 23 we converted the input dates to PLS_INTEGER numbers.

The number returned will be a decimal number, and since we do not want a decimal number – we TRUNC the number.

Then we use TO_DATE, with a second parameter “J” (for Julian date) to convert our random number to an Oracle date.

Then, the output of the code above, at MY time of running it, was:

Random date between 01/01/1940 and 01/31/1992 is: 08/28/1954
Random date between 01/01/1940 and 01/31/1992 is: 10/23/1967
Random date between 01/01/1940 and 01/31/1992 is: 03/15/1984
Random date between 01/01/1940 and 01/31/1992 is: 10/22/1991
Random date between 01/01/1940 and 01/31/1992 is: 01/06/1962
Random date between 01/01/1940 and 01/31/1992 is: 07/28/1961
Random date between 01/01/1940 and 01/31/1992 is: 06/22/1977
Random date between 01/01/1940 and 01/31/1992 is: 04/22/1950
Random date between 01/01/1940 and 01/31/1992 is: 05/07/1951
Random date between 01/01/1940 and 01/31/1992 is: 03/10/1988
Random date between 01/01/1940 and 01/31/1992 is: 09/09/1962

PL/SQL procedure successfully completed

If you get the exact result as me … it would be close to a miracle. If you do, contact me and let’s do some lottery together.

But seriously:  You should get different results each time you run the code. It is the whole purpose.

What do you say? Did it help in your project?

Hope it helps.

~ TJ