CODEWITHSUNDEEP
sqloracle-databasedateoracle11g
Paolopast
Paolopast

Reputation: 207

Random date with time in oracle

Probably it's a stupid question but I didn't find anything on this topic.

How can I get a random date with associated a random time in Oracle? I mean, I need something like 20/02/2016 18:26:30 but generated randomly. I can use the date type in oracle or I need another type?

Thanks

EDIT: I need to get a random date and time in a defined interval. How can I do something like this?

Upvotes: 1

Views: 2045

Answers (2)

Littlefoot
Littlefoot

Reputation: 142798

Would something like this do?

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> with test as
  2    (select date '2019-01-01' min_date,
  3            date '2019-01-31' max_date
  4     from dual
  5    )
  6  select min_date + dbms_random.value(0, level) new_date
  7  from test
  8  connect by level <= max_date - min_date;

NEW_DATE
-------------------
01.01.2019 09:12:46
02.01.2019 23:00:44
03.01.2019 06:53:47
03.01.2019 13:50:17
05.01.2019 05:13:33
02.01.2019 08:37:47
01.01.2019 12:07:08
08.01.2019 18:27:01
06.01.2019 18:45:57
10.01.2019 19:13:49
04.01.2019 15:22:32
02.01.2019 05:55:54
11.01.2019 20:17:50
10.01.2019 19:32:37
07.01.2019 21:54:26
06.01.2019 15:34:37
01.01.2019 20:47:33
02.01.2019 20:19:10
11.01.2019 02:00:46
07.01.2019 16:28:19
15.01.2019 11:47:29
06.01.2019 08:10:32
16.01.2019 01:00:22
14.01.2019 07:59:01
12.01.2019 10:03:16
21.01.2019 23:45:16
10.01.2019 02:34:24
17.01.2019 01:20:22
24.01.2019 07:39:40
16.01.2019 00:27:00

30 rows selected.

SQL>

Upvotes: 3

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65343

Considering DBMS_RANDOM package might be useful

select sysdate + DBMS_RANDOM.value(0,86400)/86400 * 7 from dual

for upcoming one week as an example.

Or convert 7 to 30 for upcoming one month period of time.

Demo

Upvotes: 1

Related Questions