MySQL选择带有日期的行

2021-12-19 00:00:00 sql date select mysql date-formatting

在 MySQL 中我有这个查询

In MySQL I have this query 

SELECT DISTINCT date, descr FROM book ORDER BY date

日期格式为yyyy-mm-dd

我只想选择 2012 年 1 月 的书籍.我曾尝试使用 like 但这不起作用.

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

有什么想法吗?

推荐答案

Using DATE_FORMAT 函数

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

或者使用 MONTH 和 YEAR 函数

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;

或者使用BETWEEN 功能

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;

相关文章