Mysql date sorting error

Question

SELECT
co.id,
IFNULL( oe.follow_status, 'unFollow' ) AS followStatus,
co.due_time 
FROM
car_order co
LEFT JOIN order_expire oe ON oe.order_id = co.id 
WHERE
co.type = '贷款' 
AND co.STATUS = '已放款' 
AND co.approve_status != '结清成功' 
AND date_FORMAT( co.due_time, '%Y-%m-%d' ) <= '2022-10-29' 
ORDER BY
field( oe.follow_status, 'unFollow', 'follow' ),
co.due_time DESC,
co.id DESC

Date sorting error. How should I write it?

Answer 1

SELECT
co.id,
IFNULL( oe.follow_status, 'unFollow' ) AS followStatus,
co.due_time 
FROM
car_order co
LEFT JOIN order_expire oe ON oe.order_id = co.id 
WHERE
co.type = '贷款' 
AND co.STATUS = '已放款' 
AND co.approve_status != '结清成功' 
AND co.due_time <= '2022-10-29' 
ORDER BY
field( followStatus, 'unFollow', 'follow' ),
co.due_time DESC,
co.id DESC

You should sort by the IFNULL alias followStatus instead.

Answer 2

Assuming due_time is a timestamp column, you should be able to compare it directly to a date literal:

SELECT co.id,
       IFNULL(oe.follow_status, 'unFollow') AS followStatus,
       co.due_time 
FROM car_order co
LEFT JOIN order_expire oe ON oe.order_id = co.id 
WHERE co.type = '贷款' AND 
      co.STATUS = '已放款' AND
      co.approve_status != '结清成功' AND
      co.due_time <= '2022-10-29' 
ORDER BY FIELD(oe.follow_status, 'unFollow', 'follow'),
         co.due_time DESC,
         co.id DESC;

DATE_FORMAT() The function is used to convert date/timestamp to string, but it is not needed here as date/timestamp columns can be compared directly.