Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Generating random dates between range of dates

BeefStuApr 27 2022

As part of our QA and unit testing procedures some of us are asked to help out and create process to generate or manipulate data.

I created 2 functions, which are supposed to generate random dates and timestamps between a range of dates 

I almost have this working except for a caveat that I can't seem to figure out and can use a bit of help.

If you run the INSERT query below I can seem to generate a date or timestamp for the last day of the month, which in this case is April 30, 2022 regardless of how many rows I generate. This leads me to believe I have a bug in my code, which I can use some help figuring out. I can widen the date range to May 1 2020 but I would like to understand where I went wrong.

I apologize for the two separate queries on the same data but I have yet to figure out how to combine both queries into a single query yet.

Below is my test CASE. Thanks in advance to all who respond and for your expertise.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


CREATE OR REPLACE FUNCTION random_date(
  p_from IN DATE,
  p_to   IN DATE
) RETURN DATE
IS
BEGIN
  RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_date;
/

CREATE OR REPLACE FUNCTION random_timestamp(
  p_from IN TIMESTAMP,
  p_to   IN TIMESTAMP
) RETURN TIMESTAMP
IS
BEGIN
  RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_timestamp;
/

CREATE TABLE t1 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE,
   ts TIMESTAMP 
);

 INSERT INTO t1 (dt, ts )
        SELECT
            random_date(DATE '2022-04-01', DATE '2022-04-30'),
            random_timestamp(DATE '2022-04-01', DATE '2022-04-30')
        FROM
            dual CONNECT BY level <= 10000;

/*
Missing data for April 30
*/

select trunc(dt), count(*)
from t1
group by trunc(dt)
Order by trunc(dt);

select trunc(ts), count(*)
from t1
group by trunc(ts)
Order by trunc(ts);
This post has been answered by Frank Kulash on Apr 27 2022
Jump to Answer
Comments
Post Details
Added on Apr 27 2022
2 comments
1,998 views