SQL Query On Date when type is VARCHAR

Question

I am trying to query a database with two dates ... The problem is that the column I'm querying contains dates that are currently formatted as follows: "01/01/2014" (dd / mm / yyyy) with type VARCHAR column.

Currently, I cannot convert the column to a date type.

Basically, when I query a table because it is not set to a date, the correct rows are not returned between the query ...

Does anyone else run into this problem, is there something I can change in the request?

$this->db->where('IssueDate >=', '02/12/2013');
$this->db->where('IssueDate <=', '22/01/2014');
$query = $this->db->get('MYTABLE');

Thanks guys.

Answer 1

The solution is to use str_to_date():

$this->db->where("str_to_date(IssueDate, '%d/%m/%Y') >=", "'2013-12-92'");
$this->db->where("str_to_date(IssueDate, '%d/%m/%Y') <=", "'2014-01-22'");
$

. . ISO YYYY-MM-DD - .

Answer 2

, , , ... YYYYMMDD

- .

, . , - - , , .

Answer 3

use BETWEEN with STR_TO_DATE () . Check the code below: -

$wh = STR_TO_DATE(`IssueDate`,'%d/%m/%Y')." between '02/12/2013' and '22/01/2014'";
$this->db->where($wh);

Expect this to give you great results.