0

hotel check in & check out time is

Table data

   check in                   check out                room name
2018-09-17 11:00:00           2018-09-19 11:00:00      room 1
2018-09-18 11:00:00           2018-09-19 11:00:00      room 2

Note : Standard Check in and Check out time : 11:00 AM

if i try for date from 2018-09-17 11:00:00 to 2018-09-18 11:00:00 then output for that date total booked should be 1. room 1 is booked and room 2 should be free.

my query

SELECT * FROM transcationmaster WHERE checkin >= STR_TO_DATE('2018-09-17 11:00:00', '%Y-%m-%d %H:%i:%s') and  checkout  <= STR_TO_DATE('2018-09-18 11:00:00', '%Y-%m-%d %H:%i:%s')

how to do this in mysql?

Thanks in advance

User
  • 1,334
  • 5
  • 29
  • 61
  • 4
    Possible duplicate of [Compare dates in MySQL](https://stackoverflow.com/questions/3651985/compare-dates-in-mysql) – Sfili_81 Sep 12 '18 at 06:58
  • @Sfili_81 this query use on single column but i want to check * check in * and *checkout* date both. Is this work in that query. – User Sep 12 '18 at 07:01
  • 2
    This is _not_ a duplicate of that link. This is the overlapping range problem. – Tim Biegeleisen Sep 12 '18 at 07:10

1 Answers1

2

This is just the overlapping range problem. To find records which overlap with your own date range, try this query:

SELECT *
FROM transactionmaster
WHERE '2018-09-17 11:00:00' < checkout AND '2018-09-18 11:00:00' > checkin;

enter image description here

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360