CODEWITHSUNDEEP
mysqldate
Diamonte
Diamonte

Reputation: 413

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 ?

Upvotes: 0

Views: 48

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

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;

Upvotes: 2

Related Questions