SQL Server 2019

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

CREATE TABLE tagtable (tag varchar(10), tagStart time, tagEnd time);

CREATE TABLE datetimestable (timestamp datetime)

Insert Into tagtable
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');

Insert Into datetimestable
values ('2022-07-24T23:05:23.120'),
('2022-07-27T13:24:40.650'),
('2022-07-26T09:00:00.000');

select * from tagtable
select * from datetimestable


tag
tagStart
tagEnd


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




timestamp


2022-07-24 23:05:23.120

2022-07-27 13:24:40.650

2022-07-26 09:00:00.000


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
             


date
tag


2022-07-25
tag3

2022-07-27
tag2

2022-07-26
tag1


drop table tagtable
drop table datetimestable

tag	tagStart	tagEnd
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