I need to design a query to get data between 2 time duration

Firstly, those Convert statements make me suspect you're storing your times as varchar, which is absolutely the wrong thing to do. Aside from the fact that there is no validation to ensure the column value is a valid time, it will also take up a lot more space than using the correct column type, and requires you to add those Convert statements to every query. You should look to change the type of those columns to time(0) instead.

Date and Time Data Types and Functions - SQL Server (Transact-SQL) | Microsoft Learn[^]

Secondly, the cause of your issue is clear: you are looking for records which start before the current time and end after the current time. But record 3 ends before it starts, so it can never match that condition.

One option would be to split record 3 into two records: one from 23:00 to 23:59, and one from 00:00 to 07:00.

Otherwise, you need to change your condition to account for records spanning midnight:

DECLARE @now time(0) = SYSDATETIME();

SELECT id
FROM TableName
WHERE
(
    -- Normal time range:
    (StartTime <= EndTime And StartTime <= @now And @now <= EndTime)
Or
    -- Time range spans midnight:
    (StartTime > EndTime And (StartTime <= @now Or @now <= EndTime))
);