MySQL query to select records with date-CodePudding

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?

CodePudding user response：

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)

CodePudding user response：

You havbe to convert datesm which cost a lot of processor tme, so you should avoid that and sqave all in mysql date yyyy-MM-dd hh:mm:ss

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

Last the paraMETER FOR 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;

CodePudding user response：

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.