MySQL 如何使用mysql检查日期是否在date1和date2之间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8904597/
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
How to check if a date is between date1 and date2 using mysql?
提问by SoulieBaby
I'm trying to write a query that will check today's date against my table columns date1 and date2 in mysql/php.. This is what I'm after:
我正在尝试编写一个查询，该查询将根据 mysql/php 中的表列 date1 和 date2 检查今天的日期。这就是我所追求的：
'events' table:
“事件”表：
- date1 = start date (XXXX-XX-XX)
- date2 = end date (XXXX-XX-XX)
- date1 = 开始日期 (XXXX-XX-XX)
- date2 = 结束日期 (XXXX-XX-XX)
query:
询问：
- select * from events where 2012-01-18 between date1 and date2 (or equal to date1 and date2)
- select * from events where 2012-01-18 between date1 and date2 (or equal to date1 and date2)
But I'm not sure how to go about it.. any help would be appreciated :)
但我不知道如何去做.. 任何帮助将不胜感激:)
EDIT:
编辑：
Maybe I was unclear.. if todays date = '2012-01-18' I need it to find results if today's date is between the date range of date1 and date2.. So date1 may be '2012-01-04' and date2 may be '2012-01-21'.. so if todays date falls between or on those dates, a result is returned..
也许我不清楚..如果今天的日期 = '2012-01-18' 我需要它来查找结果，如果今天的日期在 date1 和 date2 的日期范围之间..所以 date1 可能是 '2012-01-04' 和 date2可能是 '2012-01-21'.. 所以如果今天的日期介于这些日期之间或在这些日期之间，则返回结果..
回答by Eugen Rieck
SELECT * FROM events
WHERE date1<='2012-01-18'
AND date2>='2012-01-18'
回答by syn
If your referring to compare the date today is between a start and end date, I think you should use this:
如果您指的是比较今天的日期介于开始日期和结束日期之间，我认为您应该使用：
SELECT *
FROM table
WHERE '2014-08-20' >= start_date
AND '2014-08-20' <= end_date
Hope this helps :)
希望这可以帮助 ：）
回答by styfle
SELECT *
FROM events
WHERE date1 <= '2012-01-18'
AND date2 >= '2012-01-18';
This should get you started. You can use DATE(NOW())to get today's date if you don't want to hardcode a date.
这应该让你开始。DATE(NOW())如果您不想对日期进行硬编码，则可以使用来获取今天的日期。
回答by emj365
Try this,
尝试这个，
SELECT * FROM events
WHERE date1<='2012-01-19'
AND date2>='2012-01-18'
回答by Rishabh
Though there are many answers available to this question I would like to give my response regarding the same situation I faced.
虽然这个问题有很多答案，但我想就我面临的同样情况给出我的回答。
I am using php to query mysql database.
我正在使用 php 查询 mysql 数据库。
first what I do is to convert the date in the mysql supported date format which is yyyy-mm-dd
首先我要做的是将日期转换为 mysql 支持的日期格式，即 yyyy-mm-dd
$date = new DateTime($date);
$date=date_format($date, 'Y-m-d');
in the query's where clause I use BETWEEN
在查询的 where 子句中我使用BETWEEN
"'$date' BETWEEN start_date AND end_date"
this works perfectly given the case described here.
鉴于此处描述的情况，这非常有效。
回答by Silvertiger
Modified version of styfle's code
样式代码的修改版本
$date1 = '2010-01-01'; // example min
$date2 = '2015-01-01'; // example max
$sql = "SELECT * FROM events WHERE $date1 >= '2012-01-18' AND $date2 <= '2012-01-18';";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) != 0) {
// This date is equal to or within the range specified
} else {
// The date was not within the range specified
}
then you can have code executed based on the result
然后你可以根据结果执行代码
