sql - Check if a time is between two times (time DataType)

Question

I have a table that has a column "Created" as a datetime.

I'm trying to query to check if the time for the Created value is between two times.

The Created datetime for the first row is '2013-07-01 00:00:00.000' (Midnight) and I'm trying to query for items with a time between 11PM and 7AM.

select *
from MyTable
where CAST(Created as time) between '23:00:00' and '06:59:59'

But no results are returned.

Do I need to convert my times to datetimes?

Answer 1

I suspect you want to check that it's after 11pm or before 7am:

select *
from MyTable
where CAST(Created as time) >= '23:00:00' 
   or CAST(Created as time) < '07:00:00'