I have date function to create for entering date and to create random time i want to combine both of them

hi dear i have a query which create dates for specic lenght which is mentioned and a query which is create random time want to join both for date generating is this It create dates for specific lenght and insert into table query is

insert into calendar_date2  
with dates as (  
  select date'2019-12-31'+level dt  
  from   dual  
  connect by level <= 365  
)  
  select dt,   
         case   
           when to_char(dt, 'fmday') in ('sunday')   
           then 'N' else 'Y'  
         end  
  from dates
;

what it will do is enter CALENDER_DATE2 all dates mentioned on connect by level

2nd query is to genrate random time

with test (start_date) as
   (select to_date('01.01.2021 07:45', 'dd.mm.yyyy hh24:mi') from dual
 )
 select start_date + round(dbms_random.value(1, 15)) / (24 * 60) result
 from test
 connect by level <= 15

I want to write both query work together what i wrote which is not working for me that is

insert into calendar_date2  
with dates as (  
  select dates(to_date('01.01.2020 07:45', 'dd.mm.yyyy hh24:mi'))+level dt  
  from   dual
select start_date + round(dbms_random.value(1, 15)) / (24 * 60) result
 from test  
  connect by level <= 365  
)  
  select dt,   
         case   
           when to_char(dt, 'fmday') in ('sunday')   
           then 'N' else 'Y'  
         end  
  from dates
;

i write this but it not worked for me please help struck here from past many hours

Answer

You can use:

insert into calendar_date2 (dt, is_workday )
SELECT dt,
       DECODE( TRUNC( dt ) - TRUNC( dt, 'IW' ), 6, 'N', 'Y' )
FROM   (
         SELECT DATE '2020-01-01' + FLOOR( (LEVEL - 1) / 15 )
                  + FLOOR(DBMS_RANDOM.VALUE(1, 16)) * INTERVAL '1' MINUTE
                  AS dt
         FROM   DUAL
         CONNECT BY
                DATE '2020-01-01' + FLOOR( (LEVEL - 1) / 15 )
                  < ADD_MONTHS( DATE '2020-01-01', 12 )
       )

Which for the table:

  dt         DATE
             NOT NULL,
  is_workday CHAR(1)
             NOT NULL
             CONSTRAINT calendar_date2__iwd__chk CHECK ( is_workday IN ( 'Y', 'N' ) )
);

Gives you 15 rows for each day of 2020 each with random offset between 1 and 15 minutes from midnight.

Note:

• The number of days in the year is calculated so you do not need to account for leap years.
• Use FLOOR( DBMS_RANDOM.VALUE( 1, 16 ) ) rather than ROUND( DBMS_RANDOM.VALUE( 1, 15 ) ) as the first will give you an even spread of values whereas the second will only give a value 1 when the random value is between 1 and 1.5 and a value of 15 between 14.5 and 15 which will both occur with only half the frequency as the rest of the numbers and would give you an unbalanced distribution.
• You can calculate the day of the week by comparing the start of the day TRUNC( dt ) to the start of the ISO week TRUNC( dt, 'IW' ). This has the benefit that it is independent of the NLS settings and will work in all languages rather than just being limited to English speaking territories.

db<>fiddle here