CODEWITHSUNDEEP
mysqlsqlwhere-clause
pju
pju

Reputation: 21

MySQL Query - How to Filter on One Condition for Multiple Columns

I'm looking for a way to make the following query cleaner and more succinct. In my where clause, I am filtering on two dates columns that meet the same condition.

select * 
from table 
where (colA between '2021-04-01' and '2021-04-03') # same date condition
or (colB between '2021-04-01' and '2021-04-03') # same date condition

Is there a more elegant way to do this?

Upvotes: 0

Views: 982

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

I don't have a problem with your current query, and in fact I would recommend that you leave it as is. Consider the possibility that down the road one of the two date ranges might change, thus forcing you, or the inheritor of your code, to revert to this version again. But your date range logic might have a slight problem assuming colA and/or colB have a time component. Maybe use this version:

SELECT *
FROM yourTable
WHERE colA >= '2021-04-01' AND colA < '2021-04-04' OR
      colB >= '2021-04-01' AND colB < '2021-04-04';

The above date ranges include the dates 1st-April 2021 through 3rd-April 2021, inclusive on both ends, but they exclude the entire day of 4th-April 2021.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Your query is fine. But if you prefer, you could define the constants in a subquery:

select t.* 
from table t cross join
     (select '2021-04-01' as dt_start, '2021-04-03' as dt_end) params
where colA between dt_start and dt_end or   # same date condition
      colB between dt_start and dt_end      # same date condition

Upvotes: 1

Related Questions