在MySQL中使用mm/dd/yyyy格式选择数据

Question

我有下面的表结构。

Tax      |   FromDate    |   EndDate
12.32    |   12-29-2020  |   12-29-2021

税率12.32在FromDate至EndDate期间有效。当指定介于FromDate和EndDate之间的日期范围时，应选择12.32。

我尝试了下面的查询，但在传递@startdate和@enddate时，它没有给我有效的输出。

SELECT Tax
FROM Table
WHERE
DATE_FORMAT(@startDate, '%m-%d-%y') >= DATE_FORMAT(t.fromDate, '%m-%d-%y')
AND DATE_FORMAT(@endDate, '%m-%d-%y') <=  DATE_FORMAT(t.endDate, '%m-%d-%y')  LIMIT 1;

目前，我使用VARCHAR(45(作为列的DataType以及输入参数值的。我选择了VARCHAR(45(，因为在处理mm-dd-yyyy时，使用DATE类型会导致错误。

我哪里出了问题？

下面是示例代码。

SET @startDate = '07-29-2019';
SET @endDate = '09-29-2019';
DROP TEMPORARY TABLE IF EXISTS Dates;
CREATE TEMPORARY TABLE Dates (StartDate VARCHAR(20),EndDate VARCHAR(20),Tax INT);
INSERT INTO Dates VALUES ('06-29-2019','06-29-2020',1);
SELECT *
FROM Dates
WHERE
STR_TO_DATE(@startDate, '%m-%d-%y') <= STR_TO_DATE(EndDate, '%m-%d-%y')
AND STR_TO_DATE(@endDate, '%m-%d-%y') >= STR_TO_DATE(StartDate, '%m-%d-%y')

根据上面的代码，它应该返回一行。但它不会回来。

Answer 1

您想要的是STR_TO_DATE()，而不是DATE_FORMAT()。后者将日期转换为给定的字符串格式，而前者将格式化的日期字符串转换为日期(据我所知，这就是你想要的(：

SELECT Tax
FROM Table
WHERE
STR_TO_DATE(@startDate, '%m-%d-%Y') >= STR_TO_DATE(t.fromDate, '%m-%d-%Y')
AND STR_TO_DATE(@endDate, '%m-%d-%Y') <=  STR_TO_DATE(t.endDate, '%m-%d-%Y')  
ORDER BY ?
LIMIT 1;

请注意，您应该在查询中添加一个ORDER BY子句，这样LIMIT才有意义(否则，如果谓词允许多行，则未定义返回哪一行(。

我也不确定WHERE条款。如果您想要在给定日期范围参数内有效的税款，则应为：

WHERE
STR_TO_DATE(@startDate, '%m-%d-%Y') <= STR_TO_DATE(t.endDate, '%m-%d-%Y')
AND STR_TO_DATE(@endDate, '%m-%d-%Y') >= STR_TO_DATE(t.startDate, '%m-%d-%Y')