How to select latest date from database in MySQL

Question

I have list of date in MySQL in the format of "MM-DD-YYYY" and When I was trying to fetch the latest date from table it just return the last date of a Year like 12-01-2014 instead of return latest date 03-16-2016.

Payment history table:

 to_date
 03-16-2016 
 12-01-2014
 11-07-2014
 10-03-2014
 01-09-2014

---

I used following query:

SELECT MAX(to_date) FROM paymenthistory WHERE empid=59;

Result : 12-01-2014

Related post: Get the latest date from grouped MySQL data

Thanks in advance

Answer 1

You're working with strings, not native dates, so you're getting the maximum date.

Either convert those strings to ACTUAL mysql date/datetime values, or you'll have to go with ugly hacks, like

SELECT MAX(STR_TO_DATE(to_date, '%m-%d-%Y'))

and performance will be massively bad. MySQL's native date format is yyyy-mm-dd hh:mm:ss, which is a natural "most significant first" format. If your date strings were formatted like that, then even a max(string) would work.

Answer 2

It sounds like your date column is actually a VARCHAR format since it is seeing 12-01-2014 as the last date which is only true if stored as a VARCHAR.

Be sure your to_date column is a DATE type.

Answer 3

have you tried this?

SELECT TOP 1 * FROM paymenthistory WHERE empid = 29 ORDER BY to_date DESC;

Answer 4

For mysql try this

SELECT * FROM paymenthistory WHERE empid=59 ORDER BY to_date DESC LIMIT 1;