title: How to use DATE and DATE_FORMAT in CakePHP
body: <p>I am fetching data using conditions based on dates from two tables using join. I am not able to create the query perfectly, it does not fetch data according to condition. The date saved in database is in the Y-m-d H:i:s format. The condition is that the 'created_at' should be greater than 'ts_lastNewHos' in the tables. created_at is in the viewers table and ts_lastNewHos is in the users table. When I see the query by sql_dump, ts_lastNewHos is blank. The data is available in table for such conditions. What might be the error.  The query is as below:</p>
  
  <pre><code>$this-&gt;paginate = array(
          'joins' =&gt; array(
                  array(
                  'table' =&gt; 'Users',
                  'alias' =&gt; 'User',
                  'type' =&gt; 'LEFT',
                  'conditions' =&gt; array(
                      'Viewer.id = User.hosp_id',
                      'DATE(DATE_FORMAT("Y-m-d H:i:s",`created_at`)) &gt;'=&gt;DATE(DATE_FORMAT("Y-m-d H:i:s",ts_lastNewHos))  
                  )
              )
          ),
          'fields' =&gt; array('Viewer.id','Viewer.name','Viewer.address1','Viewer.state','Viewer.district','Viewer.pin_code'),
          'limit' =&gt; 10,
          'conditions'=&gt;array('DATE(DATE_FORMAT("Y-m-d H:i:s",`Viewer`.`created_at`)) &gt;'=&gt;DATE(DATE_FORMAT("Y-m-d H:i:s",`Users`.`ts_lastNewHos`)))); 
  
          $Datas = $this-&gt;paginate('Viewer');
          $this-&gt;set('Datas', $Datas); 
  </code></pre>