SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.tagtable(tag varchar(10), start time, [end] time);

INSERT dbo.tagtable(tag, start, [end]) VALUES
('tag1','04:00:00.0000000','11:59:59.9999999'),
('tag2','12:00:00.0000000','19:59:59.9999999'),
('tag3','20:00:00.0000000','03:59:59.9999999');

CREATE TABLE dbo.datetimestable([timestamp] datetime);

INSERT dbo.datetimestable([timestamp]) VALUES
('20220724 23:05:23.120'),
('20220727 13:24:40.650'),
('20220726 09:00:00.000');

;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));
   
/* the desired result would be:

date	tag
2022-07-25	tag3
2022-07-27	tag2
2022-07-26	tag1
*/

date	tag
2022-07-26	tag1
2022-07-27	tag2
2022-07-25	tag3