SQL - sorting DATE_FORMAT by weekdays from Monday to Sunday

Question

I have a query that gets distinct weekdays (data_format on the dates), and I would like to have it sorted from Monday till Sunday. Till now is weekdays alphabetically.

Query:

SELECT distinct DATE_FORMAT(cleaningdate, '%W') as contract_weekday 
FROM cleaning 
ORDER BY contract_weekday

Help appreciated. Thank you.

Answer 1

Try this,

SELECT DATE_FORMAT(cleaningdate, '%W') as contract_weekday 
FROM cleaning 
GROUP BY cleaningdate
ORDER BY DATE_FORMAT(cleaningdate, '%w')

Here i added DATE_FORMAT(cleaningdate, '%w')

%W Weekday name (Sunday..Saturday)

%w Day of the week

Answer 2

You need a numeric representation of the day to sort and you get it with weekday():

order by weekday(cleaningdate)