3

I have wrote a query to get all records greater then a date. My query is

SELECT DATE_FORMAT(`user_registered`, '%d-%m-%Y') FROM `wp_users` `a` JOIN
`wp_usermeta` `b` ON `a`.`ID`=`b`.`user_id` WHERE `b` .`meta_key` = 
'wp_capabilities' AND (DATE_FORMAT(`user_registered`, '%d-%m-%Y')> 
 '08-03-2016' );

Then my result contain some dates of 2014 and 2015 like 

| 08-12-2014                                 |
| 18-06-2015                                 |
| 08-06-2015                                 |
| 21-10-2015                                 |
| 14-07-2015                                 |
| 09-11-2015

Where I am going wrong?

halfer
  • 19,824
  • 17
  • 99
  • 186
Vinie
  • 2,983
  • 1
  • 18
  • 29
  • Incidentally, this kind of hack cannot use an index, so will be far slower than simply saying `WHERE user_registered > '2016-03-08'`. It's better if you can to handle the formatting of dates in the end user's locale and keep everything that happens 'behind-the-scenes' in yyyy-mm-dd format. – Strawberry Mar 09 '16 at 11:03
  • [this might help you](http://stackoverflow.com/a/34058692/3603374) `STR_TO_DATE` function – Sandeep Mar 09 '16 at 11:04
  • @Hytool date is stored with date time fromat. – Vinie Mar 09 '16 at 11:42
  • Well, you could simply do `DATE_FORMAT(`...`, '%Y-%m-%d')> '2016-03-08'` – Déjà vu Aug 31 '18 at 16:14

1 Answers1

1

DATE_FORMAT() returns a string, when it is compared with another string '08-03-2016', they are compared as string.

Therefore, you can see that the 'string' bigger than string '08-03..' are returned.

SELECT DATE_FORMAT(`user_registered`, '%d-%m-%Y') 
FROM `wp_users` `a` JOIN
    `wp_usermeta` `b` ON `a`.`ID`=`b`.`user_id` 
WHERE `b` .`meta_key` = 'wp_capabilities' AND 
    DATE(`user_registered`) > STR_TO_DATE('08-03-2016', '%d-%m-%Y');
Dylan Su
  • 5,975
  • 1
  • 16
  • 25