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?

similar question with an example [here][1] [1]: http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates — No Idea For Name, Jul 01 '13 at 14:58
Actually, it doesn't have to be between two days. I have a table for employee shifts. Shift 3 has a start time of 23:00:00 (11PM) and an end time of 06:59:59. Both of these are stored as the 'time' data type. — TrueEddie, Jul 01 '13 at 15:19
@t-clausen.dk: between 23h and 7h at the same day makes not much sense since the first time is later than the second and the result set is always empty. — Tim Schmelter, Jul 01 '13 at 15:20
@TimSchmelter I didn't want to get into an argument about this, so I erased my post. My logic told me that it was not important. In this case i was right, however i see other senarios where your question makes sense — t-clausen.dk, Jul 01 '13 at 15:25

Jon Skeet · Accepted Answer · 2016-11-08T06:49:59.267

59

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'

edited Nov 08 '16 at 06:49

answered Jul 01 '13 at 14:58

Jon Skeet

1,421,763
867
9,128
9,194

1

Good answer but this only applies to SQL server 2008 and newer – Mina Gabriel Jul 03 '14 at 14:43
You also need to be very careful if you are looking to group together items based on the date. If something was packed this morning at 6 AM, it would group with something else packed tonight at 11:30 PM. If you needed to group together based on date, I would suggest either adding an hour or subtracting seven hours, then performing a calculation, as necessary. – RPh_Coder Mar 21 '16 at 22:13
what if you want to check between 07:00 and 23:00 in that case this logic will not work and since time range will vary cant depend on this – George G Jan 19 '18 at 20:07
@George: Then it would be a different situation, requiring different code. It should be easy to detect which situation is applicable though. – Jon Skeet Jan 19 '18 at 20:12
@JonSkeet 'thanks a billion' <3. I've added case if start time is greater than end time and vs. much love, much appreciated, ur the hero – George G Jan 19 '18 at 20:27

score 15 · Answer 2 · answered Jul 01 '13 at 15:17

15

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

answered Jul 01 '13 at 15:17

t-clausen.dk

43,517
12
59
92

score 6 · Answer 3 · answered Nov 15 '18 at 13:19

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

score 1 · Answer 4 · answered Jul 01 '13 at 15:05

1

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

answered Jul 01 '13 at 15:05

Tim Schmelter

450,073
74
686
939

score 1 · Answer 5 · edited Jan 23 '20 at 08:14

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

edited Jan 23 '20 at 08:14

David Buck

3,752
35
31
35

answered Jan 23 '20 at 07:36

Blessie

11
1

2

When answering an old question, your answer would be much more useful to other StackOverflow users if you included some context to explain how your answer helps, particularly for a question that already has an accepted answer. See: [How do I write a good answer](https://stackoverflow.com/help/how-to-answer). – David Buck Jan 23 '20 at 07:40

Bijeesh P Bhaskar · Answer 6 · 2018-12-07T08:20:04.477

Let us consider a table which stores the shift details

enter image description here

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)

score 0 · Answer 7 · answered Dec 17 '20 at 17:45

0

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

Please check this

answered Dec 17 '20 at 17:45

Adarsh Babu PR

179
1
5

score -1 · Answer 8 · edited Oct 03 '16 at 22:50

-1

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'

edited Oct 03 '16 at 22:50

Martin Tournoij

26,737
24
105
146

answered Oct 03 '16 at 22:27

Jyhren

19
2

There is no `OR` in the question? – Martin Tournoij Oct 03 '16 at 22:50
2

A time can never be both less than `7:00` and greater than `23:00`, so the `AND` results in 0 records being returned no matter what. – MarredCheese Dec 20 '16 at 04:05

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

8 Answers8

Linked