Don't use it on AM starts with PM ends that are lower. 10:00 start, 13:00 end, would end up with the same problem you're trying to solve here. You have to use both.

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

Include some documentation in the code like that, so future devs don't have to scratch their heads and wonder what you were smoking when you wrote it. I've seen that one generate some serious confusion, since it's counter-intuitive.

The problem with it is that you end up with an index scan, instead of a seek, because of the DateAdd. Here's a workaround for that kind of thing:

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';