CODEWITHSUNDEEP
mysqldate
Malachi Bazar
Malachi Bazar

Reputation: 1825

Using MySQL how do I select data between two dates if the dates are in separate columns

In my database, I have a column with a check-in date and a column with a check-out date. I need to select every row that has a check-in date <= 7/30/2017 and a check-out date that is >= 7/30/2017.

This is the code I have now:

SELECT * 
FROM `v_reservation_records` 
WHERE cast(checkin as date) <= '7/30/2017' 
AND cast(checkout as date) >= '7/30/2017'

Here is an example date from the DB:

2018-09-18

When I run this query, I do not get any results, but I know that I have a check-in date equal to 7/30/2017. What am I missing? Or is there an easier way to accomplish my goal?

Upvotes: 0

Views: 180

Answers (2)

Kalaikumar Thangasamy
Kalaikumar Thangasamy

Reputation: 564

Try like this

SELECT * 
FROM `v_reservation_records` 
WHERE DATE_FORMAT(checkin, '%m/%d/%Y') between '7/30/2017' 
AND '7/30/2017'

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Assuming that you are casting valid values for date
You should convert also the literal the date properly 

SELECT * 
FROM `v_reservation_records` 
WHERE cast(checkin as date) <= str_to_date('7/30/2017' , '%d/%m/%Y')
AND cast(checkout as date) >= str_to_date('7/30/2017' , '%d/%m/%Y')

and you can also use between 

SELECT * 
FROM `v_reservation_records` 
WHERE str_to_date('7/30/2017','%d/%m/%Y') 
        between cast(checkin as date) AND cast(checkout as date)

Upvotes: 1

Related Questions