[FIXED] MySQL query to select records with date

Issue

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?

Solution

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;

Answered By - nbk