All geek questions in one place

Can MYSQL filter by date if the date is stored as text? ex "02/10/1984"

I am trying to change an application for a client that already has a database of more than 1000 items. Dates are stored as text in a database in the format "02/10/1984". The system allows you to add and remove fields to the directory dynamically, and also allows the advanced search to allow certain fields.

The problem is that it was not designed with dates in mind, so when I set the field as a date and try to search by range, the query tries to execute AND (cfv0.value> = 01/02/2004 AND cfv0.value <= 03/03/2008). I can make the date range be a number. Is there a way that when sending a request it takes text fields (with date) and converts them to a numerical value of time, so at this moment I basically just compare the numbers that will work fine.

I have no way to change the entire current date to a numeric value due to how dynamic fields are configured.

Thanks guys!

+3
source share
2 answers

, , ( , //):

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

, , , , , , , , , :

2004/01/20
2004/10/05
2006/12/31
+1

Source: https://habr.com/ru/post/1739512/

More articles:


All Articles