CODEWITHSUNDEEP
mysqlsqlsql-like
user12133523
user12133523

Reputation:

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.

Upvotes: 0

Views: 95

Answers (4)

Caius Jard
Caius Jard

Reputation: 74605

LIKE is only for comparing strings to a pattern that may contain wildcards; Name LIKE 'Smith%' -returns anything starting with Smith.

BETWEEN is for comparing numbers or dates to see if they fall into a range. Between includes the start and end values. BETWEEN 1 and 5 returns any records that have 1,2,3,4 or 5

They cannot be used together, you use one or the other.

Dates are like decimal numbers; the time part of a date can mean the date falls outside of the range. Just like 5.5 is not "between 1 and 5", a date time of 2019-09-29 12:34 is not "between 2019-09-20 and 2019-09-29" - only midnight on the 29th will fall in the range. For this reason I tend to prefer using > and < with date ranges:

SELECT * FROM `table_name`
WHERE
  `date_time` >= '2019-09-20' AND
  `date_time` < '2019-09-30'

This includes all records from the 20th to the 29th, no matter what time on the 29th. It's more clear and explicit than between. The other answer that has BETWEEN ... 23:59:59 is also trying to capture everything less than the 30th but it's not such a wise habit to get into, because one day the time may have milliseconds etc

It's like saying BETWEEN ... AND 9.9 because you're looking for values less than 10.. but the. One day revere is a value 9.99, and even if you used BETWEEN ... AND 9.99 one day there will be 9.999 and so on. If you want "less than 10", use < 10

Try to only use BETWEEN for value ranges you know are discrete

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

you can use subquery to get your top matching date.

select * from `table_name` where `date_time` between 
    (select `date_time` from `table_name` where `date_time` like '%2019-09-20%' limit 1)  and   
    (select `date_time` from `table_name` where `date_time` like '%2019-09-29%' limit 1)

Upvotes: 0

VahiD
VahiD

Reputation: 1064

LIKE is useless here, you want the rows that date_time between 2019-09-20 and 2019-09-29, probably inclusive. so you can use below query, without LIKE

SELECT * FROM `table_name` WHERE `date_time` BETWEEN '2019-09-20' AND '2019-09-29 23:59:59'

Upvotes: 0

Mojo Allmighty
Mojo Allmighty

Reputation: 793

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')

Upvotes: 0

Related Questions