1

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.

Unheilig
  • 16,196
  • 193
  • 68
  • 98
Bogdan Bildea
  • 11
  • 6
  • 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 May 15 '15 at 19:50
  • 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 Bessemer May 15 '15 at 19:52
  • Look at this question, http://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql – Binary Alchemist May 15 '15 at 19:54
  • Welcome to the StackOverflow community! We're looking forward to your contribution to the site! [**What should I do when someone answers my question?**](http://stackoverflow.com/help/someone-answers) – spencer7593 May 16 '15 at 13:58

1 Answers1

1

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')
spencer7593
  • 106,611
  • 15
  • 112
  • 140