DECLARE @Now time = CAST(GetDate() as time)

SELECT LoadNumber FROM LoadTimes WHERE @Now >=StartTime AND @Now < EndTime

CREATE TABLE LoadTimes(
LoadNumber tinyint,
StartTime time,
EndTime time
)

INSERT INTO LoadTimes(LoadNumber, StartTime, EndTime)
VALUES(1, '18:00', '21:00'),
(2, '21:00', '1:00'),
(3, '1:00', '18:00')

SELECT LoadNumber FROM LoadTimes WHERE @Now >= StartTime OR @Now < EndTime

DECLARE @Time1 TIME = '23:00', @Time2 TIME = '01:00';

SELECT DATEADD(hour, -12, @Time1), DATEADD(hour, -12, @Time2);

select *
from MyTable
where MyTimeColumn between @StartTime and @EndTime
or dateadd(hour, -12, MyTimeColumn) between dateadd(hour, -12, @StartTime) and dateadd(hour, -12, @EndTime); -- second computation for times across midnight barrier

CREATE TABLE #TimesTest
    (TimeColumn TIME NOT NULL);

CREATE CLUSTERED INDEX IDX_TimesTest ON #TimesTest (TimeColumn);

INSERT  INTO #TimesTest
        (TimeColumn)
        SELECT  DATEADD(SECOND, Number, 0)
        FROM    Common.dbo.Numbers;
GO
ALTER TABLE #TimesTest
ADD TimeColumn2 AS DATEADD(HOUR, -12, TimeColumn) PERSISTED;

CREATE INDEX IDX_TimeTest2 ON #TimesTest (TimeColumn2);

SET NOCOUNT ON;
GO
-- check the execution plan on this:  Index -Seek-
SELECT  *
FROM    #TimesTest
WHERE   TimeColumn2 = '13:15:45';

SELECT LoadNumber 
  FROM LoadTimes 
  WHERE (@Now >= StartTime AND (@Now < EndTime OR StartTime >= EndTime))
            OR
            (@Now <= EndTime AND  EndTime < StartTime)