MySQL 在数据库中选择一个日期范围（月/年）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14495417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select a date range (Month/Year) in database
提问by PrimuS
I have a quick question regarding selecting dateranges in MySQL, formatted like this YYYY-MM-DD
我有一个关于在 MySQL 中选择日期范围的快速问题，格式如下 YYYY-MM-DD
I read MySQL select date range issueand understand the basic usage of it, but how can I "include" the 29th of february for example? I'd like to avoid a PHP workaround, is there anything like that in MySQL?
我阅读了MySQL 选择日期范围问题并了解了它的基本用法，但是例如，我如何“包括”2 月 29 日？我想避免使用 PHP 解决方法，MySQL 中是否有类似的方法？
I can't quite understand http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
我不太明白http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
can someone give me an example how to select last years data from february?
有人能给我举个例子，如何从 2 月份选择去年的数据？
回答by bonCodigo
The moment you specify a month, MYSQL engine is smart enough to get the correct number of days for a month (whether it's a leap year or not) and evaluate your date fields accordingly.
当您指定月份时，MYSQL 引擎就足够智能，可以获取一个月的正确天数（无论是否为闰年）并相应地评估您的日期字段。
To get data between two dates of your choice: (using interval -1 yearto get one year back from today)
获取您选择的两个日期之间的数据：（interval -1 year用于从今天开始返回一年）
SELECT * FROM yourtable
WHERE yourdate BETWEEN DATE_ADD(Now(), Interval -1 YEAR)
AND Now()
AND MONTH(yourdate) = 2 -- to get data for Month of February
;
Or you can simply just write with YEAR function without BETWEEN
或者你可以简单地用 YEAR 函数编写而不 BETWEEN
SELECT * FROM yourtable
WHERE YEAR(yourdate) = YEAR(Now()) - 1
AND MONTH(yourdate) = 2 -- to get data for Month of February
;
For the question on Str_to_Datethe function is used to convert string that contains a date into a date type with a desired format and it's the inverse of DATE_FORMATfunction.
对于Str_to_Date函数的问题用于将包含日期的字符串转换为具有所需格式的日期类型，它是DATE_FORMAT函数的反函数。
SELECT STR_TO_DATE('12-Apr-2012', '%d-%M-%Y')
FROM DUAL
;
Notice in the above demo that you have to specify the day, month, year arrangement in the format you are adding into the STR_TO_DATEfunction.
请注意，在上面的演示中，您必须以添加到STR_TO_DATE函数中的格式指定日、月、年的排列方式。
PS: it's rather vague what you are really tryhing to achieve here, without sameple data and expected reslts shown in the question.. :)
PS：你真正想在这里实现的目标相当模糊，没有相同的数据和问题中显示的预期结果...... :)
回答by Minesh
Below query will give you data of last year February month
以下查询将为您提供去年二月的数据
SELECT * FROM `your_table_name` WHERE YEAR(`your_date_field_name`) = YEAR(CURRENT_DATE()) AND MONTH(`your_date_field_name`) = 2
