Check If a Time Is Between Two Times (Time Datatype)

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

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'

Check if time is between two times

select *
  from abc
  where mod(EXTRACT(HOUR FROM CAST(sysdate AS TIMESTAMP))+1,24)
between mod(EXTRACT(HOUR FROM CAST(to_date(start_time,'hh24:mi') AS TIMESTAMP))+1,24)
    and mod(EXTRACT(HOUR FROM CAST(to_date(end_time,'hh24:mi') AS TIMESTAMP))+1,24)

;

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

I think the correct logic you want is:

SELECT t.vtime
FROM time_data t LEFT JOIN
     booked_time b      
     ON t.vtime >= b.starttime AND
        t.vtime < b.endtime AND
        b.facility_id = '1' AND b.court = '1' AND
        b.on_date = '2017-02-20'
WHERE b.vtime IS NULL

This finds any matches and removes those, using a LEFT JOIN and comparison to NULL.

The alternative using NOT EXISTS seems a more natural way to do this:

select t.vtime
from time_data t
where not exists (select 1
                  from booked_time b      
                  where t.vtime >= b.starttime and
                        t.vtime < b.endtime and
                        b.facility_id = '1' and b.court = '1' and
                        b.on_date = '2017-02-20'
                 );

Select time between two times mysql

There is not a great way performance-wise to handle this. The correct where statement is:

where (start <= end and :time between start and end) or
      (end < start and :time not between end and start)

Of course, you don't have to use between, you can expand this out:

where (start <= end and :time >= start and :time <= end) or
      (end < start and (:time <= end or :time >= start))

SQL Check if available between times

It is okey actually that start could be before the new start time. But you have also check if the end is also in the between interval. Also for the comment case, you have to check if there is smaller interval.
Expand your query following way:

SELECT * FROM `items` WHERE ((`start` between '$start' and '$end') OR (`end` between '$start' and '$end') OR (`start` >= '$start' and `end` <=  '$end')) AND (`item` = $id)

How to know if an hour this in between two times with SQL Server

You can do something like this. If you get endDate < StartDate you probably should add 1 day to endDate. So you end up with:

DECLARE @HourBegin time = '18:00'
DECLARE @HourEnd time = '02:00'
DECLARE @Hour TIME = '19:00'

DECLARE @DateBegin DATETIME
DECLARE @DateEnd DATETIME
DECLARE @Date DATETIME

select @DateBegin = CAST(@HourBegin AS DATETIME)
select @DateEnd = CAST(@HourEnd AS DATETIME)
select @Date = CAST(@Hour AS DATETIME)

IF(@DateEnd < @DateBegin)
    SET @DateEnd = DATEADD(dd, 1, @DateEnd)

BEGIN TRAN 
    IF(@Date between @DateBegin and @DateEnd)
    BEGIN   
            SELECT  CONVERT(BIT,1) AS ERROR, '1'  AS MSG     
    END
    ELSE
    BEGIN   
            SELECT  CONVERT(BIT,1) AS ERROR, '0'  AS MSG     
    END
COMMIT TRAN 

SQL Check Current time is between two DATETIME columns

if you only want to check time

SELECT  *
FROM Table1 T
WHERE CAST(GETDATE() AS TIME) BETWEEN cast(T.StartDate as TIME) AND cast(T.EndDate as TIME)

How do I check if the current time is between two times in SQL?

How about

SELECT top 1 'yes'
FROM    dbo.SalesOrder
WHERE datepart(hour, GETDATE()) BETWEEN 7 and 16

SQLFiddle demo

---

Related Topics