Oracle Sql split per day based on date range and compute for total hours

February 17, 2023

I want to generate this result with the input of start date and end date. For example the start date is 2/6/2023 6:00 and end date is 2/8/2023 7:40, it should output the following

>Solution :

It is about a row generator.

(Setting date format so that it matches yours; you don’t have to do that)

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

Session altered.

test CTE contains starting values (the ones you input, somehow – via bind variables, substitution variables, whatever; I chose CTE).

I don’t know, though, where did 1.67 come from (on your screenshot), so I didn’t display it. If it is a constant, no problem. If not, what is it, then?

SQL> with test (start_date, end_date) as
  2    (select to_date('02/06/2023 06:00', 'mm/dd/yyyy hh24:mi'),
  3            to_date('02/08/2023 07:40', 'mm/dd/yyyy hh24:mi')
  4     from dual
  5    )
  6  select start_date + level - 1 start_value,
  7         trunc(start_date) + level - 1 + (end_date - trunc(end_date)) end_value
  8  from test
  9  connect by level <= end_date - start_date + 1;

START_VALUE      END_VALUE
---------------- ----------------
02/06/2023 06:00 02/06/2023 07:40
02/07/2023 06:00 02/07/2023 07:40
02/08/2023 06:00 02/08/2023 07:40

SQL>