Search code examples
mysqldatevarcharbetween

How to query date range using between in Varchar(10) format mm/dd/yyyy

I am trying to only show records where "DateDue" is within the last year. The trouble is my date is in the format of Varchar(10) as a result of my data feeds coming into my database on intervals from a vendor. Obviously, the dates are not being filtered properly using this query. Is it possible to convert this (temporarily for sake of comparing the date range) to the proper date data type for use in the query, but not permanently alter the type for that column?

SELECT `DocNum`, `DateDue` 
FROM `prod_po_list` 
WHERE `DateDue` BETWEEN (DATE_FORMAT(curdate( ) - INTERVAL 365 DAY , '%m/%d/%Y' ))
AND DATE_FORMAT( CURDATE( ) , '%m/%d/%Y' )
Solution

  • Are you looking for something like this?

     SELECT DocNum, DateDue 
 FROM prod_po_list
 WHERE STR_TO_DATE(DateDue, '%m/%d/%Y')
       BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY)
       AND CURDATE()