CODEWITHSUNDEEP
mysqldateselectbetween
Ramesh S
Ramesh S

Reputation: 661

MySql query for from date or to date exist select data

I have a table called cubbersclosure, data's added below:

|---------------------|------------------|-------------|-------------|
|      closureId      |     cubbersId    |  fromDate   |  toDate     |
|---------------------|------------------|-------------|-------------|
|          3          |         3        |  2019-07-24 | 2019-07-25  |
|---------------------|------------------|-------------|-------------|

I need to get data by following scenario:

  1. If only fromDate or toDate exist in my query get the data.

Eg: SELECT * FROM cubbersclosure cc WHERE CAST('2019-07-23' as date) <= cc.toDate AND CAST('2019-07-26' as date) >= cc.fromDate AND cc.cubbersId = '3'

The above query fromDate and toDate not same in table. So i don't want to get the data. But this query fetch the data like this : enter image description here

Upvotes: 2

Views: 240

Answers (2)

Andrews B Anthony
Andrews B Anthony

Reputation: 1381

Use the below query

SELECT * FROM cubbersclosure cc WHERE (cc.toDate=CAST('2019-07-23' as date)
OR cc.toDate=CAST('2019-07-26' as date) OR cc.fromDate=CAST('2019-07-23' as date)
OR cc.fromDate=CAST('2019-07-26' as date)) AND cc.cubbersId = '3'

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

I suspect that you want to return records for a given cubbersId which don't overlap with the from/to date range. You may try the following query:

SELECT *
FROM cubbersclosure
WHERE
    (toDate <= '2019-07-23' OR fromDate >= '2019-07-26') AND
    cubbersId = 3;

Upvotes: 1

Related Questions