MySQL BETWEEN clause with Yii PHP framework

Question

Using Yii PHP framework, I produce the following query

SELECT * FROM `purchases` WHERE (date BETWEEN '2013-12-31' AND '2015-01-01')

with this code

$exportYear = "2014";
$criteria = new CDbCriteria();
$criteria->addBetweenCondition("date", ((int)$exportYear - 1) . "-12-31", ((int)$exportYear + 1) . "-01-01");
$purchases = Purchase::model()->findAll($criteria);

What I actually need is all 'purchases' that happened in year 2014. None from a minute before or after. What does the addBetweenCondition line need to be changed to in order to accomplish this?

why not just where year(date) = 2014?

Marc B
– Marc B

2015-01-07 14:39:26 +00:00
Commented Jan 7, 2015 at 14:39 — Marc B
– Marc B, Commented Jan 7, 2015 at 14:39
@MarcB: That can't make use of indexes.

juergen d
– juergen d

2015-01-07 14:40:32 +00:00
Commented Jan 7, 2015 at 14:40 — juergen d
– juergen d, Commented Jan 7, 2015 at 14:40

Vasil Nikolov · Accepted Answer · 2015-01-07 14:51:59Z

1

The BETWEEN operator is inclusive.

If column type is DATE use '2014-01-01' AND '2014-12-31'

$exportYear = "2014";
$criteria = new CDbCriteria();
$criteria->addBetweenCondition("date", $exportYear . "-01-01", $exportYear . "-12-31");
$purchases = Purchase::model()->findAll($criteria);

If column type is DATETIME use '2014-01-01 00:00:00' AND '2014-12-31 23:59:59'

$exportYear = "2014";
$criteria = new CDbCriteria();
$criteria->addBetweenCondition("date", $exportYear . "-01-01 00:00:00", $exportYear . "-12-31 23:59:59");
$purchases = Purchase::model()->findAll($criteria);

answered Jan 7, 2015 at 14:51

Vasil Nikolov

1,12010 silver badges17 bronze badges

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

Comments

juergen d · Accepted Answer · 2015-01-07 14:38:26Z

0

SELECT * FROM `purchases` 
WHERE date BETWEEN '2014-01-01' AND '2014-12-31'

answered Jan 7, 2015 at 14:38

juergen d

205k40 gold badges305 silver badges377 bronze badges

Collectives™ on Stack Overflow

MySQL BETWEEN clause with Yii PHP framework

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related