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

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?

Should be AND instead of OR

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

Let us consider a table which stores the shift details

Please check the SQL queries to generate table and finding the schedule based on an input(time)

Declaring the Table variable

declare @MyShiftTable table(MyShift int,StartTime time,EndTime time)

Adding values to Table variable

insert into @MyShiftTable select 1,'01:17:40.3530000','02:17:40.3530000'
insert into @MyShiftTable select 2,'09:17:40.3530000','03:17:40.3530000'
insert into @MyShiftTable select 3,'10:17:40.3530000','18:17:40.3530000'

Creating another table variable with an additional field named "Flag"

declare @Temp table(MyShift int,StartTime time,EndTime time,Flag int)

Adding values to temporary table with swapping the start and end time

insert into @Temp select MyShift,case when (StartTime>EndTime) then EndTime else StartTime end,case when (StartTime>EndTime) then StartTime else EndTime end,case when (StartTime>EndTime) then 1 else 0 end from @MyShiftTable

Creating input variable to find the Shift

declare @time time=convert(time,'10:12:40.3530000')

Query to find the shift corresponding to the time supplied

select myShift from @Temp where
(@time between StartTime and EndTime and Flag=0) or (@time not between StartTime and EndTime and Flag=1)

WITH CTE as
(
SELECT CAST(ShiftStart AS DATETIME) AS ShiftStart, 
CASE WHEN ShiftStart > ShiftEnd THEN CAST(ShiftEnd AS DATETIME) +1
ELSE CAST(ShiftEnd AS DATETIME) END AS ShiftEnd
FROM **TABLE_NAME**
)
SELECT * FROM CTE
WHERE 
CAST('11:00:00' AS DATETIME) BETWEEN ShiftStart AND ShiftEnd -- Start of Shift
OR CAST('23:00:00' AS DATETIME) BETWEEN ShiftStart AND ShiftEnd -- End of Shift

select *
from MyTable
where CAST(Created as time) not between '07:00' and '22:59:59 997'

This should also work (even in SQL-Server 2005):

SELECT *
FROM dbo.MyTable
WHERE Created >= DATEADD(hh,23,DATEADD(day, DATEDIFF(day, 0, Created - 1), 0))
  AND Created <  DATEADD(hh,7,DATEADD(day, DATEDIFF(day, 0, Created), 0))

DEMO

select * from dbMaster oMaster  where ((CAST(GETDATE() as time)) between  (CAST(oMaster.DateFrom as time))  and  
(CAST(oMaster.DateTo as time)))

Please check this

I had a very similar problem and want to share my solution

Given this table (all MySQL 5.6):

create table DailySchedule
(
  id         int auto_increment primary key,
  start_time time not null,
  stop_time  time not null
);

Select all rows where a given time x (hh:mm:ss) is between start and stop time. Including the next day.

Note: replace NOW() with the any time x you like

SELECT id
FROM DailySchedule
WHERE
  (start_time < stop_time AND NOW() BETWEEN start_time AND stop_time)
  OR
  (stop_time < start_time AND NOW() < start_time AND NOW() < stop_time)
  OR
  (stop_time < start_time AND NOW() > start_time)

Results

Given

  id: 1, start_time: 10:00:00, stop_time: 15:00:00
  id: 2, start_time: 22:00:00, stop_time: 12:00:00

• Selected rows with NOW = 09:00:00: 2
• Selected rows with NOW = 14:00:00: 1
• Selected rows with NOW = 11:00:00: 1,2
• Selected rows with NOW = 20:00:00: nothing