0

I have a database where data_fine is defined as TEXT and contains values such as "25-05-2021". I need to find all the records between the current date up to 8 days.

I tried the following query, but nothing is displayed.

SELECT * from tabella_raw where data_fine > DATE(NOW) and data_fine < DATE(NOW() + INTERVAL 8 DAYS)

What is the best and safe way to compare the date stored as TEXT with the current date?

Marcus Barnet
  • 2,083
  • 6
  • 28
  • 36
  • Does this answer your question? [How to convert a string to date in MySQL?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) – Pred Dec 03 '21 at 23:00
  • Partially, the @nbk answer was the correct one I was looking for. – Marcus Barnet Dec 05 '21 at 09:22

3 Answers3

1

Try use STR_TO_DATE function

SELECT * from tabella_raw where STR_TO_DATE(data_fine, '%d-%m-%Y')  > DATE(NOW) and STR_TO_DATE(data_fine, '%d-%m-%Y') < DATE(NOW() + INTERVAL 8 DAYS)
fernandoandrade
  • 181
  • 4
1

You have to convert dates which cost a lot of processor time, so you should avoid that and save all in MySQL date yyyy-MM-dd hh:mm:ss

Also you can use CURDATE() to get the the current date

Last the parameter fo INTERVAL IS DAYnot DAYS

SELECT STR_TO_DATE("25-05-2021",'%d-%m-%Y');
SELECT * from tabella_raw where STR_TO_DATE(data_fine,'%d-%m-%Y') > Curdate() and STR_TO_DATE(data_fine,'%d-%m-%Y') < CURDATE() + INTERVAL 8 DAY;
nbk
  • 45,398
  • 8
  • 30
  • 47
  • in this case it is a FULL TABLE SCAN and no index is used – Bernd Buffen Dec 03 '21 at 23:35
  • i doubt that he has a key ` BLOB/TEXT column 'data_fine' used in key specification without a key length ?`` or else he would have switched – nbk Dec 03 '21 at 23:54
0

You are trying to use a text field as a date, so you need to convert the text to a date to use date functions.

Bill Garrett
  • 19
  • 4
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 04 '21 at 01:03