Comparing Dates In Criteria

Yii 1.1 General Discussion
1

I’v seen a similar locked post here , but the code didn’t worked.

I’m trying to compare today’s date with two date varaibles in my database. So far the query looks right , but I keep getting NULL instead on a model’s id


$Criteria = new CDbCriteria();

		$Criteria->condition = "start_date < $today AND end_date > $today";

		$period = Period::model()->findAll($Criteria);

		$this->create_year=$period ->id;

$today is date('Y-m-d) , and start_date/end_date are date fields of the model Period.

The idea is to get the period’s id for today’s date , but I keep getting NULL.

Any ideas whats wrong here?

2

Maybe as simple as:




$Criteria->addCondition('start_date < DATE(NOW()) AND end_date > DATE(NOW())');
3

Another Option:




$Criteria->condition = "start_date < CURDATE() AND end_date > CURDATE()";


 or


$Criteria->addCondition('start_date < CURDATE() AND end_date > CURDATE()');
4

Check out this wiki to make sure the dates you compare are in the same format i18n all-in-one format and timezone conversions for date time timestamp and datetime

5

As Gerhard Liebenberg said, make sure the dates you compare are in the same format. I’ve already work on comparing dates and I use this:


$Criteria->condition = "$today BETWEEN start_date AND end_date";


 or


$Criteria->addCondition("$today BETWEEN start_date AND end_date");
6

also some sql servers need $today date in single quotes


$Criteria->condition = "'$today' BETWEEN start_date AND end_date";
7

Well , yes. I tried to keep my post simple but I’m actually using a date-picker now , because I put retro-active data , thus use a variable.

However I tried NOW() and still got NULL.

They all are Y-m-d , did you mean this format?

8

If your start_date and end_date are using Y-m-d format. This should work:


$Criteria->condition = "NOW() BETWEEN start_date AND end_date";

Unless you don’t have any records that matches to the given condition it will really return NULL. If that code doesn’t work it should return error.

9

checked the records - they are fine.

Tried your code , it didn’t throw an erorr but still put NULL . . .

10

CActiveRecord::findAll() always returns an array; Try find() instead. Also, enable PHP’s error reporting for all error levels in your development environment. It makes much easier to debug problems like this.

11

Worked , but only with NOW()

When I give him a varaible , I still get NULL

This may sound stupid but I have no idea how to do that . . . .

12

Sometimes, depending of your sql server, you need to enclose the variable in single quotes.

To enable PHP’s error reporting for all error levels, you have to edit the PHP.INI file.

Search error_reporting, inside "Error handling and logging" section and change it as follow:

error_reporting=E_ALL

13

I’ll try that , thanks!