How to generate a specified range of random dates in Oracle

/*

* Oracle saves the date as an integer offset from a key date in the past (which is January 1, 4712 BC) (that is, save the date as a number);

* So you can generate a random date within a specified range by looking for an integer offset that corresponds to the ' specified date ' and ' key Date ', plus a random integer within the specified range.

*/

Steps:

1. First find the "specified date" and "Key date" corresponding to the integer offset, in the form of ' J ':

Sql>select to_char (sysdate, ' J ') from dual; --the result of the system is: 2456191

2. Generate a random date within the specified range [Min_value, Max_value]:

Sql>select to_date (2456191 + trunc (dbms_random). VALUE (Min_value, Max_value)), ' J ') from dual;

For example, to generate an arbitrary date within 2003 years:

/*

* Analysis: 2003 years of any date, that is, the date range is (2003-01-01, 2003-12-31)

* Can be converted to 2003-01-01 + (0, 365),

* Among them, 2003-01-01 is the ' appointed date '; (0, 365) is the ' specified range '

*/

1. First determine the date of January 1, 2003 integer:

Sql>select To_char (to_date (' 2003-01-01 ', ' yyyy-mm-dd '), ' J ') from dual; --the result of the system is: 2452641

2. Generate a random date within the specified range [Min_value, Max_value]:

Sql>select to_date (2452641 + trunc (dbms_random). VALUE (0, 365)), ' J ') from dual;

--dbms_random.seed

Set seed to determine the starting point for random numbers, and any change in random numbers will be determined for the same seed.

The purpose of setting the seed is to reproduce the random number for debugging.

That is, if the seed is called at a given moment, the random number that is generated sequentially is 4,6,1,...

Then, when the same seed is called again, the random number generated sequentially is still 4,6,1,...

There are two kinds of seed parameters, one is numeric, the other is character type (maximum length 2000)

PROCEDURE seed (Val in Binary_integer);

PROCEDURE seed (Val in VARCHAR2);

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/