CODEWITHSUNDEEP
sqloracle-databasedatesql-order-by
RDG
RDG

Reputation: 183

Order date by day of the week

I'm an SQL rookie and trying to figure out how to display dates in a column as the day, and then order by day of the week (i.e. Monday > Tuesday > Wednesday > etc...)

Have the first part down (displaying as the day) but when I order by my 'DAY' column it seems to order alphabetically (i.e. Monday > Friday > Saturday > etc...)

Here is my query:

SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') AS Day
FROM employees
ORDER BY Day

Any input would be much appreciated :)

Upvotes: 2

Views: 6489

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

As vkp say you probably want 

SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') AS Day
FROM employees
ORDER BY hire_date

that will show days like 

1/10/2015   thursday
2/10/2015   friday
...
30/10/2015  friday

but if you want order by day of the week not alphabetical you need use a date function different depend on your database

In sql server you have datepart

 select datename(dw,getdate()) --Friday       
 select datepart(dw,getdate()) --6

Oracle you have

select to_char(hire_date, 'D') from dual;

so order by to_char(hire_date, 'D') will give you

05/10/2015   monday
12/10/2015   monday
19/10/2015   monday
26/10/2015   monday
06/10/2015   tuesday
13/10/2015   tuesday
20/10/2015   tuesday
27/10/2015   tuesday

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use to_char(<date>, 'd') for the day of the week:

SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') AS Day
FROM employees
ORDER BY TO_CHAR(hire_date, 'D');

There might be some additional manipulation to get the proper first day, because that depends on internationalization settings.

Upvotes: 2

Related Questions