Query to search bookings between specific date and time

Question

User1487175000 posted

Hi,

I am trying to build a query in which i want to search all booking on 2020-10-10 between 10:00-15:55. However my query return booking those start 07:00 and end 07:25 as well which is i dont want it.

Here is my query, if you want to make table i also include the table query as well.

CREATE TABLE [dbo].[booking](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[bookingdate] [datetime] NOT NULL,
	[startTime] [nchar](5) NOT NULL,
	[endTime] [nchar](5) NOT NULL,
 CONSTRAINT [PK_booking] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


select * from booking

Select G.* From
(Select A.* 
From booking A
Where A.bookingdate='2020-10-10' ) G
WHERE (CAST(G.startTime as time)<=CAST('15:55' as time) AND CAST(G.startTime as time)< CAST('10:00' as time)) -- handle bookings <= 16
OR (CAST(G.startTime as time) < CAST('15:55' as time) AND CAST(G.endTime as time) > CAST('15:55' as time)) -- handle booking > 16

Here is output

Answer 1

User452040443 posted

Hi,

Try:

select * from booking
where
    bookingdate = '2020-10-10' and
    CAST(startTime as time) <= CAST('15:55' as time) and
    CAST(endTime as time) >= CAST('10:00' as time)

Hope this help

Answer 2

User-474980206 posted

well

(CAST(G.startTime as time)<=CAST('15:55' as time) AND CAST(G.startTime as time)< CAST('10:00' as time))

matches any time < 15:55. the < 10:00 is useless because all matches are also < 15:55.

why the complicated logic when you could use a simple between:

Select A.* 
From booking A
Where A.bookingdate between '2020-10-10 10:00' and '2020-10-10 15:55' ) G

this also allows using an index.

Answer 3

User1487175000 posted

matches any time < 15:55. the < 10:00 is useless because all matches are also < 15:55.

why the complicated logic when you could use a simple between:

Yes matches are waste, However logic is complicated because information in three columns.