从两个日期之间的数据库差异中检索内部数据

Question

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.

Answer 1

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));

mySQL convert varchar to date