Order the results by the day of the week

Question

Right now I'm doing an SQL HW assingment and having issues with figuring out on how to order the data from Monday to Sunday in the DAY column that was created.

The task that's required: Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week.

This is what I have so far

SELECT last_name, hire_date, to_char(hire_date,'DAY') AS "DAY" FROM employees e ORDER BY to_char(hire_date,'DAY');

However the data in the DAY column starts off with Friday, Monday, Saturday, Sunday, Thursday, Tuesday, and Wednesday.

My question is how to I fix the order by clause so that data will be sorted from Monday to Sunday?

Answer 1

Use WeekDay() in your ORDER BY instead.

SELECT 
  last_name, hire_date, to_char(hire_date,'DAY') AS "DAY" 
FROM 
  employees e 
ORDER BY 
  WeekDay(hire_date);