How to convert a date in an order by statement? (MySQL)-CodePudding

I'm trying to order my results by date, but the dates are strings, not date objects, so the results aren't showing up properly. I tried converting the date string to a date object in the sql request, but now nothing returns. There are no results showing on the screen. Here's my code:

SELECT * FROM tblnewsftb 
WHERE Status = 'Active'
ORDER BY CONVERT(datetime, ItemDate, 102) DESC

What am I doing wrong?

CodePudding user response：

In MySQL, CONVERT() only takes two arguments, the expression and a data type. The data type goes second.

Example:

CONVERT(ItemDate, DATETIME)

But this will work only if the expression (your ItemDate column in this example) is convertible as-is to a DATETIME. I would guess you're using some date format that MySQL doesn't support, like 'MM/DD/YYYY' or something else.

You'll have to use STR_TO_DATE() if you have a custom format.

Example:

STR_TO_DATE(ItemDate, '%m/%d/%Y')

It would really be best if you store datetime values in a proper DATETIME column instead of a string column if you want them to sort correctly.