Oracle random date and time

user_16771377 0
Viewed 56

I am using the code below to generate random dates, which works fine.

Can this be modified to add a random time to the date between from 00:00:00 - 23:59:59

My attempt below failed and I'm unsure why. Any help would be greatly appreciated.


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';


SELECT TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '2021-01-01','J')
                                    ,TO_CHAR(DATE '2022-12-31','J')
                                    )
                    ),'J' +
NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND')
               ) FROM DUAL;
datetime oracle
1 Answers

You have brackets in the wrong place:

SELECT TO_DATE(
         TRUNC(
           DBMS_RANDOM.VALUE(
             TO_CHAR(DATE '2021-01-01','J'),
             TO_CHAR(DATE '2022-12-31','J')
           )
         ),
         'J'
       )
       + NUMTODSINTERVAL(
           FLOOR(DBMS_RANDOM.VALUE(0,86399)),
           'SECOND'
         )
FROM   DUAL;

Note: it helps if you format your code so that indentation matches the brackets and then you can more easily spot errors like this.

You can simplify the code to:

SELECT DATE '2021-01-01'
       + NUMTODSINTERVAL(
           DBMS_RANDOM.VALUE(0, DATE '2022-12-31' - DATE '2021-01-01' + 1),
           'DAY'
         )
FROM   DUAL;

Or, even simpler:

SELECT DATE '2021-01-01'
       + DBMS_RANDOM.VALUE(0, DATE '2022-12-31' - DATE '2021-01-01' + 1)
FROM   DUAL;

db<>fiddle here

user_15092640
Related