PHP 和 MySQL：仅比较日期字段中的月份和日期，而不考虑年份

Question

我有一个日期字段（myDate）。假设该字段具有以下日期：

2015-01-01 2013-12-31

如果用户使用以下搜索参数搜索字段（myDate）：

开始日期: 2018-12-31 结束日期: 2019-01-05

我希望查询返回上面列出的 2015-01-01 和 2013-12-31 日期，因为它们位于用户提交的日期范围内（当涉及月份和日期时）

我尝试了以下查询：

$listofUsers->whereRaw("DATE(CONCAT_WS('-',YEAR(NOW()),MONTH(myDate),DAY(myDate))) >= DATE(CONCAT_WS('-',YEAR(NOW()),MONTH(?),DAY(?)))",[$frm,$frm]);
$listofUSers->whereRaw("DATE(CONCAT_WS('-',YEAR(NOW()),MONTH(myDate),DAY(myDate))) <= DATE(CONCAT_WS('-',YEAR(NOW()),MONTH(?),DAY(?)))",[$to,$to]);

它对于同一年内的日期非常有用，但如果提交的“起始日期”和“截止日期”位于两个不同的年份并且“截止日期”月份小于“起始日期”月份，则它会失败。

任何想法如何使这项工作无论年份如何

Answer 1

从你的问题来看，这不是很清楚，但如果我理解正确的话，你想要这个：SELECT *FROM tableWHERE myDate LIKE concat('%', :start_day_of_year, '%')AND   myDate LIKE concat('%', :end_day_of_year, '%')$users->whereRaw($sql, [    'start_day_of_year' => substr($fromDate, 5),     'end_day_of_year' => substr($toDate, 5), ]);

Answer 2

定义来自：用户输入至：用户输入目标：数据库字段逻辑您必须涵盖 4 种情况：逐年>变化不匹配，因为你没有覆盖一天。年复一年==。（标准）目标必须是>=“从AND” 目标必须是<=“到”。From-Year&nbsp;+1&nbsp;<To-Year（From 相对于 To 超过 1 年）全部匹配，因为您的覆盖时间超过一年年复一年+1&nbsp;==。（从去年相对于到）至>=从匹配所有内容，因为您至少覆盖了一整年。目标>=从OR目标<=到匹配。将所有内容包裹在一起NOT(1) AND (2 OR 3 OR (4 AND (4.1 OR 4.2 )))预备课程我们将日和月转换为一个简单的数字，这样比较很容易：DATE_FORMAT('2000-01-01','%m%d')将是0101=>101建筑模块从年份开始DATE_FORMAT(?,'%Y')到年度DATE_FORMAT(?,'%Y')从DATE_FORMAT(?,'%m%d')到DATE_FORMAT(?,'%m%d')目标DATE_FORMAT(myDate,'%m%d')作为 SQL-- 1NOT(&nbsp; &nbsp; DATE_FORMAT(?,'%Y') > DATE_FORMAT(?,'%Y'))AND(&nbsp; &nbsp; -- 2&nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; DATE_FORMAT(?,'%Y') = DATE_FORMAT(?,'%Y')&nbsp; &nbsp; &nbsp; &nbsp; AND&nbsp; &nbsp; &nbsp; &nbsp; DATE_FORMAT(myDate,'%m%d') >= DATE_FORMAT(?,'%m%d')&nbsp; &nbsp; &nbsp; &nbsp; AND&nbsp; &nbsp; &nbsp; &nbsp; DATE_FORMAT(myDate,'%m%d') <= DATE_FORMAT(?,'%m%d')&nbsp; &nbsp; )&nbsp; &nbsp; OR&nbsp; &nbsp; -- 3&nbsp; &nbsp; (DATE_FORMAT(?,'%Y')+1) < DATE_FORMAT(?,'%Y')&nbsp; &nbsp; OR&nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; -- 4&nbsp; &nbsp; &nbsp; &nbsp; (DATE_FORMAT(?,'%m%d')+1) = DATE_FORMAT(?,'%m%d')&nbsp; &nbsp; &nbsp; &nbsp; AND&nbsp; &nbsp; &nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- 4.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATE_FORMAT(?,'%m%d') >= DATE_FORMAT(?,'%m%d')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- 4.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATE_FORMAT(myDate,'%m%d') >= DATE_FORMAT(?,'%m%d')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATE_FORMAT(myDate,'%m%d') <= DATE_FORMAT(?,'%m%d')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ))参数[$frm, $to, $frm, $to, $frm, $to, $frm, $to, $frm, $to, $to, $frm, $frm, $to]您也许可以使用命名绑定。我不知道。（这将显着缩短参数）不是测试 - 调试愉快：D

Answer 3

您可以使用此查询$FromDate&nbsp; = '2019-02-27';$ToDate&nbsp; &nbsp; &nbsp;= '2020-06-26';SELECT * FROM `tblname`&nbsp;WHERE&nbsp;DATE(myDate) BETWEEN '$FromDate' AND '$ToDate'&nbsp;OR&nbsp;&nbsp;DATE(myDate) BETWEEN '$ToDate' AND '$FromDate';SELECT * FROM `tblname`&nbsp;WHERE&nbsp;DATE(myDate) BETWEEN '2019-02-27' AND '2020-06-26'&nbsp;OR&nbsp;&nbsp;DATE(myDate) BETWEEN '2020-06-26' AND '2019-02-27';