Question 63922 in PhpMyAdmin Question posted in
The official documentation can be found here.

MySQL query to select records with date – Phpmyadmin

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?

Tags:

3

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search