How do i order by month when using the FORMAT function

Question

I tried to do it but it keeps messing up the order for some reason. I tried DATE_FORMAT and it says it's not a recognized built-in function name

Select
  FORMAT(ProcessDate, 'MMM-yy') as ProcessDate,
  ProcessComment as ProcessComment
from EMPLOYEE
where ProcessDate between '23/06/2023' and '23/12/2023'
order by ProcessDate desc;

Answer 1

You convert the date into a string that is no longer sortable (DEC comes before FEB in the alphabet for instance). As you name the string the same as the original date, the DBMS thinks you want to sort by the string you have generated. Use an alias name that differs from the column name and sort by the original date.

You should also use proper date literals and not use strings that you hope the DBMS will convert correctly into dates for you (guessing which position is the day and which is the month for instance).

SELECT 
  FORMAT(processdate, 'MMM-yy') AS process_date,
  processcomment AS process_comment
FROM employee 
WHERE processdate BETWEEN DATE '2023-06-23' and DATE '2023-12-23' 
ORDER BY processdate DESC;

You seem to have been mistaken by tagging your request mysql. Look up the function that converts dates into strings in your DBMS. This may be FORMAT, DATE_FORMAT, TO_CHAR, or something else still. Look at the parameters, too, in order to use it correctly. As to date literals:Your DBMS may or may not support them. So this is another thing you may have to adjust.