Need Oracle SQL to split up date/time range by day

Question

I am hoping to get some help to write some SQL that I have had no success writing myself.

I have a table with the data:

ID    StartDate             EndDate
1     01/01/2000 04:30 PM   01/03/2000 06:15 AM
2     01/04/2000 08:10 AM   01/04/2000 07:25 AM
3     01/05/2000 11:00 AM   01/06/2000 03:45 AM

I need to get the following:

ID    StartDate             EndDate
1     01/01/2000 04:30 PM   01/01/2000 11:59 PM
1     01/02/2000 12:00 AM   01/02/2000 11:59 PM
1     01/03/2000 12:00 AM   01/03/2000 06:15 AM
2     01/04/2000 08:10 AM   01/04/2000 07:25 AM
3     01/05/2000 11:00 AM   01/05/2000 11:59 PM
3     01/06/2000 12:00 AM   01/06/2000 03:45 AM

In other words, split up date ranges by day. Is this even possible in SQL?

My database is Oracle 11G R2 and I am afraid due to circumstances I cannot use PL/SQL.

If not PL/SQL, what else? Why are you ignoring the last minute of each day? — cha, Jun 11 '13 at 01:54
12:00 is the first minute of the next day, so it's the start time on the next row. — Hart CO, Jun 11 '13 at 01:57
I was talking about the EndDate. The time is set at 11:59pm, which leaves a minute unaccounted for — cha, Jun 11 '13 at 02:08
@cha it might be reasonable to assume that it is 11:59:59pm but the display format doesn't show seconds — Jeffrey Kemp, Jun 11 '13 at 04:29
hey can you help me over this ??? http://stackoverflow.com/questions/22218167/need-to-split-a-date-column-into-2-solumn-as-start-date-and-end-date — Aditya Singh Rathi, Mar 06 '14 at 07:57

score 7 · Accepted Answer · answered Jun 11 '13 at 01:54

It is possible to do this in SQL. There are two tricks. The first is generating a series of numbers, which you can do with a CTE using connect.

The second is putting together the right logic to expand the dates, while keeping the right times for the beginning and end.

The following is an example:

with n as (
      select level n
      from dual connect by level <= 20
     ),
     t as (
      select 1 as id, to_date('01/01/2000 4', 'mm/dd/yyyy hh') as StartDate, to_date('01/03/2000 6', 'mm/dd/yyyy hh') as EndDate from dual union all
      select 2 as id, to_date('01/04/2000 8', 'mm/dd/yyyy hh') as StartDate, to_date('01/04/2000 12', 'mm/dd/yyyy hh') as EndDate from dual union all
      select 3 as id, to_date('01/05/2000', 'mm/dd/yyyy') as StartDate, to_date('01/06/2000', 'mm/dd/yyyy') as EndDate from dual
     )
select t.id,
       (case when n = 1 then StartDate
             else trunc(StartDate + n - 1)
        end) as StartDate,
       (case when trunc(StartDate + n - 1) = trunc(enddate)
             then enddate
             else trunc(StartDate + n)
        end)
from t join
     n
     on StartDate + n - 1 <= EndDate
order by id, StartDate

Here it is on SQLFiddle.

I cannot thank you enough, this is exactly the help I was looking for. I now realize that I need to study the connect by command much closer and as a bonus I learned about sqlfiddle! — , Jun 11 '13 at 02:25

Geraldo Assis · Answer 2 · 2017-03-06T15:01:03.310

-1

Thanks Gordon! It helped me a lot too. My unique comment is that I had to change the join clause from:

on StartDate + n - 1 <= EndDate

To:

on trunc(StartDate + n - 1) <= trunc(EndDate)

After this change it worked for me perfectly.

edited Mar 06 '17 at 15:01

answered Mar 03 '17 at 16:25

Geraldo Assis

1
1

Need Oracle SQL to split up date/time range by day

2 Answers2

Linked