This query is driving me crazy, any help is appreciated.
I have a table as follows:
CREATE TABLE Bookings
(
Id bigInt IDENTITY(1,1),
hallId bigInt,
startTime smallDateTime,
endTime smallDateTime
)
INSERT INTO Bookings
VALUES (1, '2022-10-03 08:00:00', '2022-10-03 10:00:00')
--Edit( One of my trials so far
DECLARE @startTime AS SmallDateTime = '2022-10-03 08:00:00'
DECLARE @endTime AS SmallDateTime = '2022-10-03 10:0:00'
DECLARE @hallId AS bigInt = 1
SELECT * FROM Bookings WHERE
startTime >= FORMAT(@startTime, 'yyyy-dd-MM HH:mm:ss') OR startTime <= FORMAT(@endTime, 'yyyy-dd-MM HH:mm:ss') AND
endTime >= FORMAT(@startTime, 'yyyy-dd-MM HH:mm:ss') OR endtime <= FORMAT(@endTime, 'yyyy-dd-MM HH:mm:ss')
AND hallId=@hallId
-- Also this Way
DECLARE @startTime AS SmallDateTime = '2022-03-10 08:00:00'
DECLARE @endTime AS SmallDateTime = '2022-03-10 10:0:00'
DECLARE @hallId AS bigInt = 1
SELECT * FROM Bookings WHERE
startTime >= @startTime OR startTime <= @endTime
AND
endTime >= @startTime OR endtime <= @endTime
AND hallId=@hallId
--Edit)
My Datetime format is 'yyyy-MM-dd HH:mm:ss' but it's records are entered in yyyy-dd-MM HH:mm:ss format
So what I want is a query that checks for 2 datetime ranges (say 2022-10-03 08:00:00 - 2022-10-03 10:00:00), if there is a match it returns the data otherwise it returns nothing.
My goal is to check if either of the startTime OR endTime falls within an existing Booking (e.g None of startTime or endTime can't be within '2022-10-03 08:00:00' up to '2022-10-03 10:00:00'), if found, the booking can't precede unless he/she has to alter. I tried between, but seems to work one way and fails another
It would be a plus if someone could check in separately that is to display a specific message/status for startTime and endTime (I mean if the startTime corresponds to existing Booking msg='Select a different start time' and if The endTime corresponds to existing Booking msg='Select a different end time'
Also I tried to check if the difference of startTime and endTime is less then 1 hour.
BWT, I'm using SQL Server 2014 Express
You can do
INSERT...SELECT...WHERE NOT EXISTS
, then check the rowcount afterwards. To compare for interval overlap, compare one start time with the other end, and the reverse.You may want to change to
<=
if you don't want to allow two intervals to abut.