Finding orders placed between two times gives zero result

I’m not sure what’s happening here. I’m trying to select the orders that were placed between 4PM and 4AM, so from 1600 to 0400. The date does not matter, just the time. This is what I’ve tried so far.

Here is the code for the table:

declare @ords as table (
    id numeric(5,0),
    ord_date datetime
)

insert into @ords values
(55489,'2021-06-01 08:00:00'),
(87496,'2021-04-23 16:29:00'),
(51323,'2021-04-14 03:08:00')

;

I selected using several ways:

select distinct *
from @ords o
where cast(o.ord_date as time) between '16:00:00' and '04:00:00'

;

select distinct *
from @ords o
where convert(varchar,o.ord_date,8) between '16:00:00' and '04:00:00'

;

select distinct o.*, convert(varchar,o.ord_date,8)
from @ords o
where convert(varchar,o.ord_date,8) between convert(varchar,'16:00:00',8) and convert(varchar,'04:00:00',8)

;

select distinct o.*, convert(varchar,o.ord_date,8)
from @ords o
where cast(o.ord_date as time) between cast('16:00:00' as time) and cast('04:00:00' as time)

;

I keep getting nothing in return. What am I missing here?

When I select the converted or cast values I’m using, they look normal to me.

SSMS version is 18.8

!!!EDIT: I’ve also just tried using > and that worked. And also > and <= in my where, but that didn’t work.

Answer

between '16:00:00' and '04:00:00' can never be true. '04:00:00' is before '16:00:00' so there are no times between them. BETWEEN is effectively parsed as the following:

WHERE CAST(o.ord_date AS time) >= '16:00:00'
  AND CAST(o.ord_date AS time) <= '04:00:00'

It’s like asking for a number less than or equal to 4, but greater than or equal to 16. No such number exists.

It seems what you actually want, however, is this:

WHERE (CAST(o.ord_date AS time) <= '04:00:00'
   OR  CAST(o.ord_date AS time) >= '16:00:00')