query to find a partial match

Question

I want to write a query to find all the items in my 'schedule_items' table that match a YYYY-MM-DD value of a DATETIME field.

something like: SELECT * FROM schedule_items WHERE start ?matches? '$date'

Where start would be a DATETIME field with data like 2009-09-23 11:34:00 and $date would 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 my DATETIME start field?

Answer 1

You can use LIKE in MySQL

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

You put a % sign as a wildcard.

Be aware though that like can be a very heavy query. Specially if you use % at the start of a string.

Answer 2

in SQL you can use LIKE instead of = and then you get the % wildcard. Example:

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

(That would match anything from August 2009)

Answer 3

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

Where 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 might not be the only/best solution : there are functions that deal with dates ; and you probably can use comparison operators too.

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'll 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 get you every data for which the date is more recent than $date.

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

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

This might be better than "like", if you start column has an index -- not sure, though ; but, still, what you are willing to get will be obvious from your query... And that is nice.