CODEWITHSUNDEEP
mysqlsqldatewildcard
Mike Corrato
Mike Corrato

Reputation: 85

Query for rows within a date range that also have a specified substring

I'm trying to query a MySQL database and return all records within a given date range and which also contain the substring 'bank' in the content column.

The format of the 'time' field I refer to is mm/dd/yyyy hh:mm:ss.

Here's the statement I've come up with but MySQL Workbench is giving me issues:

SELECT * 
FROM blogs 
WHERE ((‘time’ BETWEEN “04/01/2011 00:00:00” AND “04/15/2011 23:59:59”) 
   AND (‘content’ LIKE ‘%bank%’))

Upvotes: 2

Views: 63

Answers (2)

Jason
Jason

Reputation: 15335

How about trying this: 

SELECT * 
    FROM blogs 
    WHERE `time` BETWEEN '2011-04-01 00:00:00' AND '2011-04-15 23:59:59' 
        AND `content` LIKE '%bank%';

This works if your time field is in fact a timestamp. If time is not a timestamp then you will have to go with something like the answer from McAdam331 but I'm hoping your database is using the correct types for the kind of data you are asking it to store.

single ' or double " quotes around values and ticks ` around field names. I also changed the date format to yyyy-mm-dd hh:mm:ss and eliminated some unnecessary parentheses.

http://sqlfiddle.com/#!9/730bd/1/0

It would be helpful if you posted the structure of the table when posting questions like this so we can be sure to give the right answer.

Upvotes: 2

AdamMc331
AdamMc331

Reputation: 16691

It isn't a good idea to store dates like that in MySQL. The DBMS has Date and Time Types you can use to store that information.

If changing the database isn't an option, you can convert a string to a date object using the STR_TO_DATE function, which takes in a date string and the format that it is in already and returns a date.

MySQL stores dates in the 'YYYY-MM-DD' format, so to get that format you can try something like this:

SELECT STR_TO_DATE('04/01/2011', '%m/%d/%Y');

Which will return a date object for that day. Note the capital Y.

Then it becomes much easier to query between dates, like this:

SELECT *
FROM myTable
WHERE STR_TO_DATE(dateString, '%m/%d/%Y %H:%i:%s') BETWEEN STR_TO_DATE('04/01/2011 00:00:00', '%m/%d/%Y %H:%i:%s') AND STR_TO_DATE('04/15/2011 23:59:59', '%m/%d/%Y %H:%i:%s')
      AND content LIKE '%bank%';

Here is an SQL Fiddle example, and here is a link that has the formatting characters you need.

Upvotes: 1

Related Questions