从两个日期之间的数据库差异中检索内部数据
I have a table named TestingLogDevice. Table column name dateee. Table dateee field value looks like this.
id dateee
1 01-11-18
2 01-11-18
3 01-11-18
4 01-11-18
5 31-11-18
6 31-11-18
7 31-11-18
8 31-11-18
9 31-11-18
10 30-11-18
11 30-11-18
12 29-11-18
13 28-11-18
14 28-11-18
15 27-10-18
I have a form looks like this. When I submit this form value. Then I get this value looks like this
Array
(
[precise_chart_val] => Array
(
[id] =>
[start_date] => 30-10-18
[end_date] => 01-11-18
)
)
I retrieve the value two date difference from table TestingLogDevice using this code. I follow this link Tutorial Link
$start_date = $data['precise_chart_val']['start_date'];
$end_date = $data['precise_chart_val']['end_date'];
$conditions = array('TestingLogDevice.dateee BETWEEN ? and ?' => array($start_date, $end_date));
$this->set('datas',
$this->TestingLogDevice->find('all',
array(
'conditions' =>array($conditions, 'TestingLogDevice.Siteid'=>$testing_log_device_site_name),
'order' => array('id' => 'desc')
)
)
);
But when I print datas I get all value of dateee field from id 1 to 15. But I get only data from id 1 to 11. Where I'm wrong? I don't fix it.
my dateee field is vachar.
You are getting this result because of your dateee field datatype. To solve this problem Either change data type from varchar to datetime
OR
Change
$conditions = array('TestingLogDevice.dateee BETWEEN ? and ?' => array($start_date, $end_date));
To
$conditions = array("date_format(str_to_date(TestingLogDevice.dateee, '%d-%m-%Y'), '%d-%m-%Y') BETWEEN ? and ?" => array($start_date, $end_date));