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,033
  • 6
  • 28
  • 32
  • 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
  • 31,930
  • 6
  • 24
  • 40
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