Selecting data using mm/dd/yyyy format in MySQL

I have the below table structure.

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

Tax rate 12.32 is valid from FromDate to EndDate. 12.32 should be selected when specify a date range which is between the FromDate and EndDate.

I tried the below query, however it doesn't give me the valid output when pass the @startdate and @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;

Currently I am using VARCHAR(45) as the DataType for the columns as well as the for the input parameter values. I selected VARCHAR(45), because using DATE type throws me an error when dealing with mm-dd-yyyy.

Where I have gone wrong?

Below is the sample code.

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')

According to the above code, it should return a row. But its not returning.

Solution

You want STR_TO_DATE(), not DATE_FORMAT(). The latter converts a date to a given string format, while the former converts a formatted date string to a date (which, as I understand your question, is what you are looking for):

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;

Note that you should add a ORDER BY clause to your query so LIMIT makes sense (otherwise, it is undefined which row will be returned if predicates allow multiple rows).

I am also unsure about the WHERE clause. If you want taxes that are valid during the given date range parameter, that should be:

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')