Evaluating datetime into timewindows

The only tricky bit here is that your tag time ranges can go over midnight, so you need to check that your time is either between start and end, or if it spans midnight its between start and 23:59:59 or between 00:00:00 and end.

The only other piece is splitting your timestamp column into date and time using a CTE, to save having to repeat the cast.

;WITH splitTimes AS
(
    SELECT CAST(timestamp AS DATE) as D,
     CAST(timestamp AS TIME) AS T
    FROM @datetimestable
)
SELECT 
DATEADD(
   day,
   CASE WHEN b.[end]<b.start THEN 1 ELSE 0 END,
   a.D) as timestamp,
b.[tag]
FROM [splitTimes] a
INNER JOIN @tagtable b 
   ON a.T between b.[start] and b.[end]
   OR (b.[end]<b.start AND (a.T BETWEEN b.[start] AND '23:59:59.99999'
   OR a.T BETWEEN '00:00:00' AND b.[end]))

Live example: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=506aef05b5a761afaf1f67a6d729446c

Since they're all 8-hour shifts, we can essentially ignore the end (though, generally, trying to say an end time is some specific precision of milliseconds will lead to a bad time if you ever use a different data type (see the first section here) - so if the shift length will change, just put the beginning of the next shift and use >= start AND < end instead of BETWEEN).

;WITH d AS 
(
  SELECT datetime = [timestamp],
    date = CONVERT(datetime, CONVERT(date, [timestamp])) 
  FROM dbo.datetimestable
)
SELECT date = DATEADD(DAY, 
    CASE WHEN t.start > t.[end] THEN 1 ELSE 0 END, 
    CONVERT(date, date)), 
  t.tag
FROM d 
INNER JOIN dbo.tagtable AS t
  ON  d.datetime >= DATEADD(HOUR, DATEPART(HOUR, t.start), d.date)
  AND d.datetime <  DATEADD(HOUR, 8, DATEADD(HOUR, 
                    DATEPART(HOUR, t.start), d.date));

• Example db<>fiddle

Maybe I am looking at this to simple, but,
can't you just take the first tag with an hour greater then your hour in table datetimestable.
With an order by desc it should always give you the correct tag.
This will work well as long as you have no gaps in your tagtable

select case when datepart(hour, tag.tagStart) > 19 then dateadd(day, 1, convert(date, dt.timestamp))
            else convert(date, dt.timestamp)
       end as [date],
       tag.tag
from   datetimestable dt 
  outer apply ( select top 1
                       tt.tag,
                       tt.tagStart
                from   tagtable tt
                where  datepart(Hour, dt.timestamp) > datepart(hour, tt.tagStart)
                order by tt.tagStart desc
             ) tag  

It returns the correct result in this DBFiddle

The result is

EDIT

If it is possible that there are gaps in the table,

then I think the most easy and solid solution would be to split that row that passes midnight into 2 rows, and then your query can be very simple
See this DBFiddle

select case when datepart(hour, tag.tagStart) > 19 then dateadd(day, 1, convert(date, dt.timestamp))
            else convert(date, dt.timestamp)
       end as [date],
       tag.tag
from   datetimestable dt 
  outer apply ( select tt.tag,
                       tt.tagStart
                from   tagtable tt
                where  datepart(Hour, dt.timestamp) >= datepart(hour, tt.tagStart)
                and    datepart(Hour, dt.timestamp) <= datepart(hour, tt.tagEnd)
             ) tag

Here's a completely different approach that defines the intervals in terms of starts and durations rather than starts and ends.

This allows the creation of tags that can span multiple days, which might seem like an odd capability to have here, but there might be a use for it if we add some more conditions down the line. For example, say we want to be able say "anything from 6pm friday to 9am monday gets the 'out of hours' tag". Then we could add a day of week predicate to the tag definition, and still use the duration-based interval.

I have defined the duration granularity in terms of hours, but of course this can easily be changed

create table #tags
(
   tag varchar(10),
   startTimeInclusive time,
   durationHours int
);

insert   #tags
values   ('tag1','04:00:00', 8),
         ('tag2','12:00:00', 8),
         ('tag3','20:00:00', 8);

create table #dateTimes (dt datetime)

insert   #dateTimes
values   ('2022-07-24T23:05:23.120'),
         ('2022-07-27T13:24:40.650'),
         ('2022-07-26T09:00:00.000');

select   dt.dt,
         t.tag
from     #datetimes     dt
join     #tags          t  on cast(dt.dt as time) >= t.startTimeInclusive
                              and dt.dt < dateadd
                              (
                                 hour,
                                 t.durationHours,
                                 cast(cast(dt.dt as date) as datetime) -- strip the time from dt
                                 + cast(t.startTimeInclusive as datetime) -- add back the time from t
                              );