MySQL选择具有日期的行 [英] MySQL select rows with date like

问题描述

在MySQL中我有这个查询

In MySQL I have this query

SELECT DISTINCT date, descr FROM book ORDER BY date

日期格式为 yyyy-mm-dd

我想仅选择2012年1月的图书。我试图使用，如，但这不起作用。

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

任何想法？

推荐答案

使用 DATE_FORMAT 功能

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 功能

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;

这篇关于MySQL选择具有日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！