Split single row into multiple rows based on Date and time in SQL Server

I want to split a single row into multiple rows based on time. below are the example.

SrNo    Notification    StartDate                  EndDate
---------------------------------------------------------------------------
1       001003741915    2018-08-20 07:27:00.000    2018-08-21 16:23:00.000
2       001003779670    2018-08-21 03:36:00.000    2018-08-21 04:36:00.000
3       001003779830    2018-08-21 04:36:00.000    2018-08-21 21:35:00.000

Expected output is below:

SrNo    Notification    StartDate                  EndDate
---------------------------------------------------------------------------
1       001003741915    2018-08-20 07:27:00.000    2018-08-21 05:59:00.000
1       001003741915    2018-08-21 06:00:00.000    2018-08-21 16:23:00.000
2       001003779670    2018-08-21 03:36:00.000    2018-08-21 04:36:00.000
3       001003779830    2018-08-21 04:36:00.000    2018-08-21 05:59:00.000
3       001003779830    2018-08-21 06:00:00.000    2018-08-21 21:35:00.000

Day start from 06:00 AM to next day 06:00 AM. When EndDate time is grated than 06:00 AM then split this date in two rows. first row end date is 2018-08-21 05:59:00.000 and next row start 2018-08-21 06:00:00.000.

Answer

Below Query will help you.

CREATE TABLE #test
(
    Notifications varchar(50)
    ,StartDate datetime
    ,EndDate Datetime
    ,Id int
)

INSERT into #test
select              '001003741915','2018-08-20 07:27:00.000','2018-08-21 16:23:00.000',1
UNION select        '001003779670','2018-08-21 03:36:00.000','2018-08-21 04:36:00.000',2
UNION select        '001003779830','2018-08-21 04:36:00.000','2018-08-21 21:35:00.000',3
UNION select        '001003779835','2018-08-21 04:36:00.000','2018-08-24 21:35:00.000',4

;with cte
As (  SELECT 
        ID,Notifications,StartDate,dateadd(d, datediff(d, 1, StartDate+1), '06:00') as StartOfDay, EndDate,dateadd(d, datediff(d, 1, EndDate+1), '06:00')  as EndDayOfDate
    FROM #test
)
, Result
AS (


    select Id
            ,Notifications
            ,StartDate 
            ,CASE WHEN StartOfDay BETWEEN StartDate AND EndDate THEN  StartOfDay
                  WHEN ENDDate <StartOfDay THEN ENDDate
                  WHEN ENDDate <EndDayOfDate THEN ENDDate
                ELSE  EndDayOfDate  END AS  EndDate 
    from cte

    union ALL
    Select T.Id
            ,T.Notifications
            ,R.EndDate As StartDate
            ,CASE WHEN R.EndDate+1 < T.EndDate THEN R.EndDate+1 ELSE  T.EndDate   END AS EndDate 
    from cte  T
    INNER JOIN Result R
    ON R.Notifications=T.Notifications
    WHERE  R.EndDate <T.EndDate

)

SELECT * FROM Result order by id