MySQL select rows with date like

问题:

In MySQL I have this query

SELECT DISTINCT date, descr FROM book ORDER BY date

Date is in format yyyy-mm-dd

I want to select only the the books from January 2012. I have tried to use like but that does not work.

Any ideas?

回答1:

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;

See this SQLFiddle

回答2:

You can use >= and <= operators here. Check the below code:

SELECT *
FROM book
WHERE date >= '2012-01-01' AND date <= '2012-01-31'

回答3:

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.

回答4:

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;

回答5:

SELECT DISTINCT date, descr
FROM book
WHERE YEAR = DATE(NOW()) AND MONTH(date) = '1'

This will give you this years books

回答6:

Using like also works. With @hims056 fiddle, you can test it:

SELECT DISTINCT ID, date FROM book 
WHERE date LIKE '2012-01%'
ORDER BY date;

However, it's not usual to use a like for date filtering, for me it's more natural to use >= and <= , or between. Also, there's a performance benefit.