MySQL comparing stored date with current date and execute

Question

I have stored dates (dd/mm/yyyy) in text format in a table in a field called dates. I want to compare those dates with the current date and if the dates are smaller (if the dates have passed) to move the entire row into a new table called archive. Tried something with the DATEDIFF() but I'm new to MySQL and can't figure it out.

Welcome to Stack Overflow! This question is a little short on information. Can you share what you have tried, and what problems you have run into? — Jay Blanchard
First, stop storing dates as a string (char/varchar/text) and start storing it as a date. There is a reason for the DATE column type. Then see if you still need to ask this question. — Devon
Look at this question, stackoverflow.com/questions/5201383/… — Binary Alchemist
Welcome to the StackOverflow community! We're looking forward to your contribution to the site! What should I do when someone answers my question? — spencer7593

spencer7593 spencer7593 · Accepted Answer · 2015-05-15T21:00:06

I'm going to preface my answer with a short remark: storing "date" values in SQL database in VARCHAR columns is an anti-pattern. MySQL provides native datatype DATE which is designed to handle "date" values. But that's just a remark, doesn't answer your question.

You can use the convenient MySQL STR_TO_DATE function to convert strings into DATE values. For example:

STR_TO_DATE('15/05/2015','%d/%m/%Y')

You could use a column reference in place of the literal, e.g.

STR_TO_DATE(t.mycharcol,'%d/%m/%Y')

and that will return a DATE value you can compare to another DATE value, using the standard inequality operator < for example.

To return the current date from the database, you can use an expression such as

DATE(NOW())

Putting that together, you could write a query like this:

SELECT t.*
  FROM t
 WHERE STR_TO_DATE(t.mycharcol,'%d/%m/%Y') < DATE(NOW())

If you want to take the result from a SELECT statement and insert those rows into another table, you can use the INSERT ... SELECT form of the INSERT statement.

Reference: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Beware of the behavior with badly formatted or invalid dates, e.g.

 SELECT STR_TO_DATE('35/05/2015','%d/%m/%Y')
      , STR_TO_DATE('15-05-2015','%d/%m/%Y')

MySQL comparing stored date with current date and execute

1 Answers