4

I want to get records from the MySQL database with date today or later. The data is recorded into the database as VARCHAR (fieldname datum), so I need to use STR_TO_DATE. However, this query is not working:

SELECT * FROM Diensten WHERE STR_TO_DATE('datum', '%m-%d-%Y') >= DATE(NOW()) ORDER BY STR_TO_DATE('datum', '%m-%d-%Y') ASC

I also tried CURDATE(), doesn't work either.

The query is working without the WHERE part. Any ideas how to fix the query?

William
  • 67
  • 1
  • 4

1 Answers1

3

This expression STR_TO_DATE('datum', '%m-%d-%Y') returns NULL because the column name was quoted using single quote. It doesn't convert the value of column datum but string datum is converted that is why it results to a NULL value.

To fix, just remove the single quotes around the column name.

SELECT * 
FROM Diensten 
WHERE STR_TO_DATE(datum, '%d-%m-%Y') >= CURDATE()
ORDER BY STR_TO_DATE(datum, '%d-%m-%Y') ASC
Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    followup question, why are you storing dates as string in the database? – John Woo Jul 20 '13 at 16:44
  • Thank you. This is resulting in one record shown, but there are 4: 21-07-2013 23-07-2013 02-08-2013 12-08-2013 I only see 12-08-2013 now – William Jul 20 '13 at 16:46
  • it should be `'%d-%m-%Y'` not `'%m-%d-%Y'`. the other three results to `NULL` because month is only upto `12`. – John Woo Jul 20 '13 at 16:48
  • Stupid :( Thank you very much! The database is already in use, someone else designed it, that is the reason why dates are in there as string. – William Jul 20 '13 at 16:51