MySQL select rows with date like
You can use >= and <= operators here. Check the below code:
SELECT *
FROM book
WHERE date >= '2012-01-01' AND date <= '2012-01-31'
If you are adamant that you want to use the LIKE syntax, you can convert the date to CHAR first:
SELECT DISTINCT date, descr FROM book WHERE CAST(date AS char) LIKE '2012-01%' ORDER BY date;
Try this:
SELECT DISTINCT date, descr FROM book WHERE YEAR(date) = '2012' and MONTH(date) = '1'
This works if your "date"-column is a MySQL date field.
Using DATE_FORMAT function
SELECT DISTINCT date, descr FROM book
WHERE DATE_FORMAT(date, '%Y %m') = DATE_FORMAT('2012-01-01', '%Y %m')
ORDER BY date
Or using MONTH and YEAR functions
SELECT DISTINCT date, descr FROM book
WHERE Month(date) = Month('2012-01-01')
AND Year(date) = Year('2012-01-01')
ORDER BY date;
Or using BETWEEN functions
SELECT DISTINCT date, descr FROM book
WHERE date BETWEEN '2012-01-01'
AND '2012-01-31'
ORDER BY date;
Or using <= and >= operators
SELECT DISTINCT date, descr FROM book
WHERE date >= '2012-01-01'
AND date <= '2012-01-31'
ORDER BY date;