Data … as usual

All things about data by Laurent Leturgez

Generate a random date in Oracle

This morning, I had to generate a random date using DBMS_RANDOM package. As the documentation mentions this package can only generate alphanumeric and numeric value, I have to search a little bit.

So the trick for generating a random date is to choose two dates between the random process will choose a date.

Those dates will be translated to Julian format. As the Julian format is the number of days since January 1, 4712 BC, you can use the numeric random generation procedure of DBMS_RANDOM package.

Let’s see how we can generate a random date between 2009 Jan, 1st and 2009 Dec, 31st :

SQL> create table t (d date);
Table created.

SQL> select to_char(to_date('2009/01/01','YYYY/MM/DD'),'J') "20090101",
2 to_char(to_date('2009/12/31','YYYY/MM/DD'),'J') "20091231"
3 from dual;

2009010 2009123
------- -------
2454833 2455197

SQL> begin
2 for i in 1..5 loop
3 insert into t values(to_date(trunc(dbms_random.value(2454833,2455197)),'J'));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select * from t;
2009-NOV-30 12AM:00:00
2009-MAY-31 12AM:00:00
2009-SEP-01 12AM:00:00
2009-DEC-28 12AM:00:00
2009-MAY-03 12AM:00:00

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: