Generate Random Date between Dates or Period Using Julian Date and DBMS_RANDOM package

advertisements

_____________________________________________________________________________________________________________________

What is Julian date format?

In Oracle a date can be formatted into Julian format which will be a number and known as Julian Day. The Julian number will be calculated by counting the number of days from 1^st of January 4712 BC to the date.

See examples

SQL> SELECT TO_CHAR(sysdate,'J') FROM DUAL;

TO_CHAR

-------

2456050

SQL> select to_date (2456050,'J') from dual;

TO_DATE(2

---------

02-MAY-12

Now we will discuss about to generate random number between a period using Julian and DBMS_RANDOM function.

In below example I have generated a random date between 01-01-2010 and 01-01-2012. You can change the dates depends on the requirement in below mentioned example

SQL> SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(

TO_CHAR(TO_DATE('01-01-2010','dd-mm-yyyy'),'J'),

           TO_CHAR(TO_DATE('01-01-2012','dd-mm-yyyy'),'J'))),'J')

FROM DUAL    ;

TO_DATE(T

---------

01-FEB-11

SQL> /

TO_DATE(T

---------

02-DEC-11

SQL> /

TO_DATE(T

---------

03-APR-11

_____________________________________________________________________________________________________________________

Website Stats