Diamonte Diamonte - 4 months ago 11
MySQL Question

MySQL compare custom formatted date to now

I have in mysql, varchar column where the dates are stored in format d/m/Y, thought older inputs are only m/Y.

I have query to select the oldest record:

SELECT expire
FROM logs
WHERE log_id = :logid AND STR_TO_DATE(expire, '%d/%m/%Y') > NOW()
ORDER BY STR_TO_DATE(expire, '%d/%m/%Y') ASC LIMIT 0,1"


This however does not return anything if date is stored in old format without 'd' (as m/Y) in database, how I can return the row with AND without d ?

Answer

You can check the length of the str

SELECT expire 
FROM logs 
WHERE log_id = :logid 
AND  CASE length(expire) 
        WHEN 10 THEN   STR_TO_DATE(expire, '%d/%m/%Y') > NOW() 
        WHEN 7  THEN  STR_TO_DATE(expire, '%m/%Y) > NOW() 
      END
ORDER BY STR_TO_DATE(expire, '%d/%m/%Y') ASC LIMIT 0,1;