Search code examples
mysqlsqlsql-like

I have problem to use like and where clause together in sql

I want to select data from the SQL table and fetch data from the table with the use of LIKE and BETWEEN clause together and I have tried below query.

SELECT * FROM `table_name` WHERE  `date_time` LIKE BETWEEN '%2019-09-20%' AND '%2019-09-29%';

but it shows me the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN '%2019-09-20%' AND '%2019-09-29%' LIMIT 0, 25' at line 1

can anybody help me with this.

Solution

  • You can treat date_time as an actual date.

    SELECT * FROM `table_name` WHERE STR_TO_DATE(`date_time`, '%Y-%m-%d') BETWEEN STR_TO_DATE('2019-09-20', '%Y-%m-%d') AND STR_TO_DATE('2019-09-29', '%Y-%m-%d')