如果日期存储为文本，MYSQL可以按日期过滤吗？ ex“02/10/1984” [英] Can MYSQL filter by date if date is stored as text? ex "02/10/1984"

问题描述

我正在尝试修改已有超过1000个项目的数据库的客户端的应用程序。日期作为文本存储在数据库中，格式为02/10/1984。系统允许您动态地向目录添加和删除字段，并且还允许高级搜索允许特定字段。

I am trying to modify an app for a client which has already a database of over 1000 items. The dates are stored as text in the database with the format "02/10/1984". The system allows you to add and remove fields to the catalog dynamically and it also allows the advanced search to have specific fields be allowed.

问题是，它的设计并不考虑日期，所以当我将一个字段设置为日期，并尝试按照一个范围搜索查询正在尝试做一个AND（cfv0.value> = 01/02/2004 AND cfv0.value< = 05/03/2008）。我可以使它所以传递的日期范围是一个数值时间值。有一种方法，当发送查询，它需要文本字段（与日期），并将其转换为数值时间值，所以在那一点，我基本上只是比较数字，将工作正常。

The problem is that it wasn't designed with dates in mind, so when I set a field as a date, and try to search by a range the query is trying to do a AND (cfv0.value >= 01/02/2004 AND cfv0.value <= 05/03/2008) . I can make it so the date range passed is a numeric time value. Is there a way that when sending the query, it takes the text fields (with the date) and converts it to numeric time value so at that point I am basically just comparing numbers which would work fine.

由于动态字段的设置方式，我没有选择将当前日期更改为数字值。

I do not have the option to change all the current date to numeric value due to the way the dynamic fields are set up.

谢谢！

推荐答案

您将要转换查询和列中的日期，按月/日/年顺序）：

You'll want to convert both the query coming in and the date in the column, so something like this (assuming it's in month/day/year order):

AND STR_TO_DATE(cfv0.value, '%m/%d/%Y') >= STR_TO_DATE('01/02/2004', '%m/%d/%Y')

这篇关于如果日期存储为文本，MYSQL可以按日期过滤吗？ ex“02/10/1984”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！