How to generate a specified range of random dates in Oracle

Source: Internet
Author: User


* 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.



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;


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);


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.