create table #MachineLog (ID int , startDate date, startTime time , endTime time)
insert into #MachineLog select 287106,'2023-03-07','22:43:37.0000000','01:15:25.0000000'
insert into #MachineLog select 287105,'2023-03-08','00:38:09.0000000','00:54:17.0000000'
insert into #MachineLog select 287091,'2023-03-07','23:15:45.0000000','01:15:25.0000000'
insert into #MachineLog select 287092,'2023-03-08','00:18:09.0000000','02:35:09.0000000'
ID, STARTDATE, STARTTIME, ENDTIME
DateName("hh", STARTTIME) between 0 and 9
DateName("hh", ENDTIME) between 0 and 9
So what you really want are those records where start time is between midnight and 9:00 AM AND endtime is between midnight and 9:00 AM. If so,
startTime >= '00:00:00.0000000' AND startTime < '09:00:00.0000000'
AND endTime >= '00:00:00.0000000' AND startTime < '09:00:00.0000000'
In your example on the first row, I am guessing that the end time is on the following day, i.e, 2023-03-08. So you could alternatively write your query to specify that starttime should be less than end time, like this:
startTime >= '00:00:00.0000000'
AND endTime < '09:00:00.0000000'
AND startTime < endTime;
BTW, What @harishgg1 posted should also work. Avoiding functions in where clause is a good practice for allowing query optimizer to use available indexes. For this reason, I prefer the queries I posted here.