Clever Geek Handbook

Query to find a partial match

I want to write a query to find all the elements in my table 'schedule_items'

that match the value of a YYYY-MM-DD

field DATETIME

.

something like: SELECT * FROM

schedule_items WHERE

start?matches? '$date'

Where the start will be a field DATETIME

with type data 2009-09-23 11:34:00

and $date

will be something like 2009-09-23

Can I do this or do I need to load all the values ​​and then explode and compare the values ​​in the DATETIME field start

?

+2


source to share


4 answers


You can use LIKE in MySQL

SELECT * FROM schedule_items WHERE start LIKE 'some-date%'


You are putting the% sign as a wildcard.

Remember that this can be a very difficult request. Especially if you use% at the beginning of a line.

+6


source


If you are working with dates, it looks like you are saying:

Where the start would be a DATETIME field with data like 2009-09-23 11:34:00 and $ date would be something like 2009-09-23

Using LIKE may not be the only / best solution: there are functions that relate to dates; and you can probably use comparison operators as well.

In your case, you can probably use something like this:

select * 
from headers_sites 
where date_fetch >= '2009-07-15' 
limit 0, 10

Of course, you will have to adapt this query to your tables / fields; something like this might do, I suppose:



SELECT * FROM schedule_items WHERE start >= '$date'

This will give you all data for which the date is later than $ date.

If you only want the date of one day, this can do:

SELECT * 
FROM schedule_items 
WHERE start >= '$date'
    and start < adddate('$date', interval 1 day)

It may be better than "like" if you start a column that has an index - not sure though; but, nevertheless, what you are ready to receive will be obvious from your request ... And this is nice.

+2


source


in SQL you can use LIKE instead =

and then you get the template %

. Example:

select ... where start like "2009-09-%";

(This will match anything as of August 2009)

+1


source


If you need to extract date records from a datetime field, you need to use the following format:

SELECT * FROM schedule_items WHERE date(start) = 'some-date time'

0


source






More articles:


All Articles