MySQL查询 - 今天和最近30天之间的记录 [英] MySQL Query - Records between Today and Last 30 Days

问题描述

我想返回在过去30天内添加到数据库的所有记录。由于显示目的，我需要将日期转换为mm / dd / yy。

I want to return all records that were added to the database within the last 30 days. I need to convert the date to mm/dd/yy because of display purposes.

create_date between DATE_FORMAT(curdate(),'%m/%d/%Y') AND (DATE_FORMAT(curdate() - interval 30 day,'%m/%d/%Y')) 

将记录限制在过去30天 - 它会选择所有记录。

My statement fails to limit the records to the last 30 days - it selects all the records.

任何人都可以指向正确的方向？感觉就像我很近。

Can anyone point me in the right direction? It feels like I am close.

感谢并有一个愉快的一周。

Thanks and have a great week.

推荐答案

您需要在 SELECT 子句中应用 DATE_FORMAT ，而不是 WHERE 子句：

You need to apply DATE_FORMAT in the SELECT clause, not the WHERE clause:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

另请注意， CURDATE（）仅返回日期的 DATE 部分，所以如果您存储 create_date 作为 DATETIME ，时间部分填充，此查询将不会选择今天的记录。

Also note that CURDATE() returns only the DATE portion of the date, so if you store create_date as a DATETIME with the time portion filled, this query will not select the today's records.

在这种情况下，您需要使用 NOW ：

In this case, you'll need to use NOW instead:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

这篇关于MySQL查询 - 今天和最近30天之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！