Oracle: How to split records across days I have data with two timestamp columns startdate and enddate. The data is produced continuously everyday and some records are such that the startdate is one day and the enddate is the nextday as it passes midnight. For example:

Original Record:

No.   StartDate                        EndDate
1    2014-01-10 20:50:04.45           2014-01-11 01:15:25.67


Transformed Record:

No.   StartDate                        EndDate
1     2014-01-10 20:50:04.45          2014-01-10 23:59:59.99
1     2014-01-11 00:00:00.00          2014-01-11 01:15:25.67


How do I do this in Oracle?
There are several ways to do this.  One would be to use a pipelined function to return the additional row when the day spanning condition is detected.  Another way is to duplicate the rows and then eliminate the ones that don't need to be duplicated and adjust the times for those that do.  Here is an example:

SELECT 
   Case When TwoRowsNeeded = 1 And RowNumber = 2 Then Trunc(EndDate)
        Else StartDate
   End StartDate,
   Case When TwoRowsNeeded = 1 AND RowNumber = 1 Then Trunc(EndDate)-1/24/60/60
        Else EndDate
   End EndDate
FROM
(
SELECT StartDate, EndDate
   , Row_Number() OVER (PARTITION BY StartDate, EndDate ORDER BY NULL) RowNumber
   , trunc(EndDate,'DD')-trunc(Startdate,'DD') TwoRowsNeeded
FROM t1
CROSS JOIN (select level from dual connect by level &lt;=2)
)
WHERE RowNumber=1 Or TwoRowsNeeded=1;


Setup:

drop table t1;
create table t1 as (
   select trunc(sysdate) StartDate, trunc(sysdate)+2/24 EndDate from dual
   UNION ALL
   select trunc(sysdate)-1, trunc(sysdate)-1+2/24 from dual
   UNION ALL
   select trunc(sysdate)-2/24, trunc(sysdate)-3/24 from dual
   UNION ALL
   select trunc(sysdate)-2/24, trunc(sysdate)+1/24 from dual
   UNION ALL
   select trunc(sysdate)-1/24/60, trunc(sysdate)+1/24/60 from dual
   );

SELECT to_char(StartDate,'MM/DD/YYYY HH24:MI:SS') StartDate
   , to_char(EndDate,'MM/DD/YYYY HH24:MI:SS') EndDate 
FROM t1;