0

I'm trying to get the latest record from a table based on the date. My date format is mm/yyyy. I've tried using MAX(date) but it's getting the record based on the month only. I've also tried using MONTH(MAX(date)) but returns a null value. Any help is much appreciated. By the way, I'm currently using XAMPP if that helps.

Kent
  • 177
  • 1
  • 19
  • Can you share the create table statement, also add some sample data and desired output. –  May 15 '18 at 03:07
  • 2
    `mm/yyyy` is not a date. A date consists of a month, a day, and a year. What data type is your column? – Ken White May 15 '18 at 03:07
  • Are you storing a date as a varchar? Yuck! Fix this ASAP! – The Impaler May 15 '18 at 03:12
  • I'm required to use the mm/yyyy format so I'm using VARCHAR as opposed to the DATE format which is MM/DD/YYYY @TheImpaler – Kent May 15 '18 at 04:30
  • So you're not using dates, so stop trying to use date-related functions to work with them. Date-related functions are designed to work with actual dates (month, day, year), and not some kludged-up VARCHAR value. – Ken White May 15 '18 at 12:10

3 Answers3

1

Dates don't have formats. In MySQL, you can use:

select t.*
from t
order by right(date, 4) desc, left(date, 2) asc
limit 1;

You should learn to use the built-in data types for dates and date/times.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Try parsing the string format to date format before filtering, you can refer to this post:

how to convert a string to date in mysql?

pedrodotnet
  • 788
  • 3
  • 16
  • 34
-1

Did you try with :

SELECT * FROM tables [WHERE conditions] ORDER BY expression DESC;

The order expression will be date field name you want to order.

MrBac
  • 17
  • 5