SQLite中m/d/yyyy格式日期范围查询返回错误结果的技术问询

你遇到的这个问题，核心在于SQLite是把你的Date字段当作字符串来比较，而不是日期类型。

SQLite本身没有专门的日期/时间类型，默认会把存储的日期文本按字符串规则来排序和比较。你的日期格式是m/d/yyyy，这种格式的字符串在做BETWEEN比较时，是逐字符按ASCII码值对比的：

• 比如'1/20/2015'和'1/9/2015'比较，前三个字符是'1/2' vs '1/9'，第三个字符'2'的ASCII码（50）比'9'（57）小，所以SQLite会认为'1/20/2015'小于'1/9/2015'，自然就被包含在你的查询结果里了。
• 同理，所有以1/2开头的日期（比如1/20、1/21...）都会被判定为介于'1/2/2015'和'1/9/2015'之间，因为字符串比较到第三个字符时就得出了“前者更小”的结论，不会继续往后看日的十位数字。

有几种可行的方式来修正这个问题：

方法1：使用date()函数转换日期格式 #

SQLite的date()函数可以将不同格式的日期字符串转换为标准的yyyy-MM-dd格式，这样就能正确按日期逻辑比较了。修改你的查询语句如下：

SELECT Date FROM table_nm 
WHERE date(Date, '%m/%d/%Y') BETWEEN '2015-01-02' AND '2015-01-09';

这里的'%m/%d/%Y'是告诉date()函数你的原始日期格式，转换后就会按真实的日期范围过滤数据。

方法2：统一存储为标准日期格式（推荐） #

如果可能的话，建议把Date字段的存储格式改成yyyy-MM-dd或者SQLite支持的其他标准日期格式（比如yyyyMMdd）。这样后续所有的日期查询、排序操作都能直接按预期工作，不需要每次都转换格式。

方法3：手动拼接成可比较的字符串 #

如果不想用date()函数，也可以手动把m/d/yyyy格式的日期拼接成yyyyMMdd的数字字符串来比较，比如：

SELECT Date FROM table_nm 
WHERE 
  substr(Date, instr(Date, '/', instr(Date, '/') + 1) + 1) || 
  substr('0' || substr(Date, 1, instr(Date, '/') - 1), -2) || 
  substr('0' || substr(Date, instr(Date, '/') + 1, instr(Date, '/', instr(Date, '/') + 1) - instr(Date, '/') - 1), -2)
BETWEEN '20150102' AND '20150109';

不过这种写法比较繁琐，不如用date()函数简洁，只作为备选方案。

内容的提问来源于stack exchange，提问作者Student