SQL查询类型为VARCHAR时的日期 [英] SQL Query On Date When Type Is VARCHAR
问题描述
我试图查询一个数据库之间有两个日期...问题是,我查询的列包含当前格式化为01/01/2014(dd / mm / yyyy)的日期,列类型为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');
感谢各位。
推荐答案
解决方案是使用 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这样的常数 - 大多数数据库都能正确接受。
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.
这篇关于SQL查询类型为VARCHAR时的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!