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

Question

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?

Then just use '2017-07-30' instead of '7/30/2017'. And don't cast date to date. — Paul Spiegel
– Paul Spiegel, Commented Sep 11, 2017 at 19:40
SELECT * FROM v_reservation_records WHERE checkin <= '2017-30-7' AND checkout >= '2017-30-7' — Malachi Bazar
– Malachi Bazar, Commented Sep 11, 2017 at 19:44
Running this still returns no results. If you're right though, it looks like I have a formatting problem instead of a coding problem. :) — Malachi Bazar
– Malachi Bazar, Commented Sep 11, 2017 at 19:46

ScaisEdge · Accepted Answer · 2017-09-11 19:29:30Z

1

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)

answered Sep 11, 2017 at 19:29

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Malachi Bazar Over a year ago

It turns out that your second answer solved the problem. Thanks!

Malachi Bazar Over a year ago

@Paul Spiegel, thanks for your troubleshooting as well!

Kalaikumar Thangasamy · Accepted Answer · 2017-09-11 19:46:15Z

0

Try like this

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

answered Sep 11, 2017 at 19:46

Kalaikumar Thangasamy

5642 silver badges8 bronze badges

2 Comments

Bill Karwin Over a year ago

This is not a good idea, because it spoils the possibility of using an index. Just use date values in the canonical YYYY-MM-DD format.

Malachi Bazar Over a year ago

This would not work because the check out date is not guaranteed to be the date that I'm searching for.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related