CODEWITHSUNDEEP
mysqlsqlmax
Gorakh
Gorakh

Reputation: 97

How to select latest date from database in MySQL

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

Upvotes: 0

Views: 8025

Answers (4)

Sadikhasan
Sadikhasan

Reputation: 18600

For mysql try this 

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

Upvotes: 0

Gian Carlo
Gian Carlo

Reputation: 215

have you tried this?

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

Upvotes: 0

davidethell
davidethell

Reputation: 12018

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.

Upvotes: 0

Marc B
Marc B

Reputation: 360562

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.

Upvotes: 2

Related Questions