Query for value between two COLUMNS

Yii 2.0 General Discussions
1

I want to generate a query as follows:

SELECT *
FROM `test_date`
WHERE '2020-05-01' BETWEEN `start_date` AND `end_date`

I have tried the following:

return $this->find()
    ->where(['BETWEEN', $date, 'start_date', 'end_date'])
    ->all();

When I do this I get the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '2020-05-01' in 'where clause'
The SQL being executed was: SELECT * FROM `test_date` WHERE `2020-05-01` BETWEEN 'start_date' AND 'end_date'

So it seems it tries to backtick the value as if it was a column. How do I tell it that the value is not the column and the columns are not the values?

2

You can do the following. It is effectively the same SQL:

SELECT *
FROM `test_date`
WHERE '2020-05-01' > `start_date` AND '2020-05-01' < `end_date`
3

Alternatively you can form the query as string:

$this->andWhere(':date BETWEEN [[start]] AND [[end]]');