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

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 1st 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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google