locked
Query to search bookings between specific date and time RRS feed

  • 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

    Friday, November 13, 2020 3:31 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 13, 2020 8:23 PM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 13, 2020 8:23 PM
  • 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.

    Friday, November 13, 2020 8:40 PM
  • 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.

    Saturday, November 14, 2020 11:12 AM