Splitting up events that occur over the day boundary

I have a table of events with a start time and an end time, with some events that have a start time before midnight and an end time after midnight. I’d like to produce output that splits up these events at the midnight barrier so they can be counted toward their respective date.

| EVENT_ID | START_TIME              | END_TIME                |
|----------|-------------------------|-------------------------|
| 1001     | 2021-02-21 14:00:00.000 | 2021-02-21 18:00:00.000 |
| 1002     | 2021-02-21 17:00:00.000 | 2021-02-22 03:00:00.000 |
| 1003     | 2021-02-21 18:00:00.000 | 2021-02-21 22:00:00.000 |
| 1004     | 2021-02-21 22:00:00.000 | 2021-02-22 07:00:00.000 | 

The above table could be produced by the query:

SELECT EVENT_ID,
       START_TIME,
       END_TIME
FROM EVENTS
WHERE START_TIME BETWEEN '2021-02-21 00:00:00.000' AND '2021-02-21 23:59:59.999'
;

My desired output will split up the events that span multiple days at midnight:

| EVENT_ID | START_TIME              | END_TIME                |
|----------|-------------------------|-------------------------|
| 1001     | 2021-02-21 14:00:00.000 | 2021-02-21 18:00:00.000 |
| 1002     | 2021-02-21 17:00:00.000 | 2021-02-21 23:59:59.999 |
| 1002     | 2021-02-22 00:00:00.000 | 2021-02-22 03:00:00.000 |
| 1003     | 2021-02-21 18:00:00.000 | 2021-02-21 22:00:00.000 |
| 1004     | 2021-02-21 22:00:00.000 | 2021-02-21 23:59:59.999 |
| 1004     | 2021-02-22 00:00:00.000 | 2021-02-22 07:00:00.000 | 

Any help would be greatly appreciated. Ideally I’d like to produce this without functions or the creation of new tables.

Note that I’m using SQL Server 2016

Answer

Using table of numbers

with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),nmbs as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1 cross join t0 t2 cross join t0 t3
)
select event_id, 
             case when n = 0 
                   then start_time
                   else dateadd(day, n, convert(date, start_time))
             end start_time,
             case when datediff(day, start_time, end_time) = n
                   then end_time
                   else dateadd(second, -1, dateadd(day, n + 1, convert(datetime, convert(date, start_time))))
             end as end_time
from Events
cross apply (
  select top (datediff(day, start_time, end_time) + 1) n 
  from nmbs) ns