Sometimes data spans hours. This code takes something that begins in hour 1 and may not end till hour 2 or 3. It splits it into it's hourly components.

• HN is hour number

create function [dbo].[ufn_splithours]( @start datetime, @duration int ) returns @res table (
 hn int, starttime datetime, endtime datetime 
)

as
begin
declare @actualend datetime
set @actualend=dateadd(second,@duration, @start)
declare @sdx datetime
set @sdx=dbo.DateOnly(@start)


declare @hnx int
declare @tx table (hn int, starttime datetime, endtime datetime)
SET @HNx=0
WHILE @HNx < 24
BEGIN
  INSERT INTO @Tx SELECT @HNx, @SDx, Dateadd(hh,1,@sdx)
  SET @SDx=DATEADD(HH,1,@SDx)
  SET @HNx=@HNx+1
END



declare @endtime datetime
declare @hourend datetime
declare @hourstart datetime

declare @dtx datetime
set @dtx =@start
declare @rowsx int
set @rowsx=0
while @dtx < @actualend
begin

  	 
   if @rowsx =0
   begin
     -- first entry

	 set @hnx=datepart(hour,@dtx)
	 select @hourstart=starttime, @hourend=endtime from @tx where hn=@hnx
   
     insert into @res select @hnx, @start, @hourend
   
	 set @rowsx=@rowsx+1
   end
   set @dtx=dateadd(second,1,@dtx)
   if datepart(hh, @dtx ) > @hnx
   begin
	 set @hnx=datepart(hour,@dtx)
	 select @hourstart=starttime, @hourend=endtime from @tx where hn=@hnx
   
     if @actualend < @hourend
	   set @hourend=@actualend

   
	 insert into @res select @hnx, @hourstart, @hourend
	 set @rowsx=@rowsx + 1
   end
   
      
end
DELETE FROM @res WHERE STARTTIME=ENDTIME

return
  

end

create  function [dbo].[DateOnly](@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
    begin
    return dateadd(dd,0, datediff(dd,0,@DateTime))
    end