CODEWITHSUNDEEP
phpmysql
ian
ian

Reputation: 12335

query to find a partial match

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 * FROMschedule_itemsWHEREstart?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?

Upvotes: 3

Views: 3964

Answers (4)

Saif Muhammad Abdul Razzak
Saif Muhammad Abdul Razzak

Reputation: 23

If it is needed to extract records of a date from datetime field, you need to use following format:

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

Upvotes: 1

Pascal MARTIN
Pascal MARTIN

Reputation: 400952

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.

Upvotes: 2

JasonWoof
JasonWoof

Reputation: 4196

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)

Upvotes: 1

&#211;lafur Waage
&#211;lafur Waage

Reputation: 69991

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.

Upvotes: 8

Related Questions