CODEWITHSUNDEEP
phpmysql
eScoo
eScoo

Reputation: 151

MySQL PHP - Compare String Date

I've got a problem comparing my date column to the current date. My Date column is a String ('13.12.2013 17:12 - Fr.'). I cannot make it a date field, cause it is only given as a String in Joomla Datetime picker.

I have to select all rows with the date > current date. I've tried some solutions, but nothing works. My current solution is as followed, but it still shows all rows, not only the ones where date > current date.

SELECT * FROM jtmpl2_chronoforms_data_test_reservierung 
WHERE res_bestaetigt = 'yes' AND res_deleted = 0 
    AND STR_TO_DATE(res_date, '%d.%m.%Y') > DATE_FORMAT(CURDATE(), '%d.%m.%Y') 
ORDER BY STR_TO_DATE(res_date, '%d.%m.%Y %H:%i') ASC"

Can anyone help please?

Upvotes: 0

Views: 256

Answers (2)

Daniel82
Daniel82

Reputation: 101

You are doing 2 opposing things with STR_TO_DATE(res_date, '%d.%m.%Y') > DATE_FORMAT(CURDATE(), '%d.%m.%Y')

The first part creates a mysql DateTime value, which is what you want. The secont part creates a string out of a DataTime value. So you are comparing a datetime vs. a string. Change it the following way: STR_TO_DATE(res_date, '%d.%m.%Y') > CURDATE()

You probably also have to move this condition to the "HAVING" part.

Upvotes: 1

quickshiftin
quickshiftin

Reputation: 69681

It looks like your DATE_FORMAT(CURDATE(), '%d.%m.%Y') is coming out differently than the STR_TO_DATE(res_date, '%d.%m.%Y') piece, thus failing comparison. Have a look

mysql> select DATE(STR_TO_DATE('13.12.2013 17:12 - Fr.', '%d.%m.%Y'));
+---------------------------------------------------------+
| DATE(STR_TO_DATE('13.12.2013 17:12 - Fr.', '%d.%m.%Y')) |
+---------------------------------------------------------+
| 2013-12-13                                              |
+---------------------------------------------------------+

mysql> select DATE_FORMAT(CURDATE(), '%d.%m.%Y');
+------------------------------------+
| DATE_FORMAT(CURDATE(), '%d.%m.%Y') |
+------------------------------------+
| 16.12.2013                         |
+------------------------------------+

Try using NOW() for the later piece

mysql> select DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2013-12-16  |
+-------------+

then the comparison works fine

mysql> select DATE(STR_TO_DATE('13.12.2013 17:12 - Fr.', '%d.%m.%Y')) > DATE(NOW());
+-----------------------------------------------------------------+
| DATE(STR_TO_DATE('13.12.2013 17:12 - Fr.', '%d.%m.%Y')) > NOW() |
+-----------------------------------------------------------------+
|                                                               0 |
+-----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE(STR_TO_DATE('13.12.2013 17:12 - Fr.', '%d.%m.%Y')) < DATE(NOW());
+-----------------------------------------------------------------+
| DATE(STR_TO_DATE('13.12.2013 17:12 - Fr.', '%d.%m.%Y')) < NOW() |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Upvotes: 0

Related Questions