Format a string to a date in MySQL query

Question

I have a table as shown below, the date column is a string(MMMM-yyyy).

I want to select the latest row for an ID. For ID # 2, the latest date would be August-2020 with the price of 45.40

ID   Date         Price
2    August-2020  45.40    
2    July-2020    42.30

I've tried the format(date, 'MMMM-yyyy) as formatted date and STR_TO_DATE(date, '%MMMM-%yyyy') but I can't get it to convert to a date so I can order the column. I want to keep the date in this format, I just need to order it for my query.

Answer 1

Use str_to_date() to convert the string to a date; a little trick is needed to make the original value a complete date string before conversion, so:

str_to_date(concat(date, '-01'), '%M-%Y-%d')

To filter the table on the latest date per id, you would do:

select t.*
from mytable t
where date = (
    select date
    from mytable t1
    where t1.id = t.id
    order by str_to_date(concat(date, '-01'), '%M-%Y-%d') desc
    limit 1
)