SQL Query On Date When Type Is VARCHAR

问题:

I'm trying to query a database with a between 2 dates... The problem is the column that I am querying contains dates that are currently formatted like this "01/01/2014" (dd/mm/yyyy) with a column type of VARCHAR.

At the moment, I can't convert the column to a date type.

Basically when I query the table because it's not set to date type the between query doesn't return the correct rows...

Has anyone else come across this problem, is there something I can change within the query?

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

Thanks guys.

回答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'");
$

You may not have any control over the database. But you do have control over your own constants. You should get used to the ISO standard YYYY-MM-DD for such constants -- unambiguous and accepted correctly by most databases.

回答2:

I might suggest creating a view on the table in the database that transforms the string date columns you have into the following format... YYYYMMDD

That format is sortable and can easily be compared versus other similar formatted dates - you can even do date arithmetic with it.

Keep in mind that a view does not copy the table or add any performance overhead. It is often a good idea to access any table through a view even if initially you do not need to perform any manipulations on the underlying table - it will help if you later find you do need to perform them.

回答3:

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

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

Expect it'll give You perfect result.