Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY the day of the week (ORACLE DB SQL)
ORDER BY the day of the week [message #608179] Mon, 17 February 2014 03:49 Go to next message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Please assist.
I have my code below and want to sort by the day of the week.
My code does sort that column but as alphabetic and not days.

DEFINE day = TO_CHAR(hire_date, 'fmDAY')
SELECT last_name, hire_date, &day AS "Day"
FROM employees
ORDER BY &day;
Re: ORDER BY the day of the week [message #608182 is a reply to message #608179] Mon, 17 February 2014 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 11063
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you need to order by the numeric representation of the day of the week:
SQL> SELECT to_char(SYSDATE, 'D') FROM dual;
 
TO_CHAR(SYSDATE,'D')
--------------------
1
Re: ORDER BY the day of the week [message #608184 is a reply to message #608182] Mon, 17 February 2014 04:09 Go to previous messageGo to next message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Thanks this is my new code and it works, however is there a way to make it start from Monday and not Sunday?

DEFINE day = TO_CHAR(hire_date, 'D')
SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') AS "Day"
FROM employees
ORDER BY &day;

Re: ORDER BY the day of the week [message #608185 is a reply to message #608179] Mon, 17 February 2014 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also note that "ORDER BY &day;" does not order anything as "&day" is a constant during the query execution.

Re: ORDER BY the day of the week [message #608187 is a reply to message #608184] Mon, 17 February 2014 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
to make it start from Monday and not Sunday


The number of a day in a week and the first day of the week depends on your territory:
SQL> alter session set nls_territory=america nls_date_language=american;

Session altered.

SQL> select to_char(sysdate+level-2,'D Dy DD/MM/YYYY') from dual connect by level <= 7;
TO_CHAR(SYSDATE+
----------------
1 Sun 16/02/2014
2 Mon 17/02/2014
3 Tue 18/02/2014
4 Wed 19/02/2014
5 Thu 20/02/2014
6 Fri 21/02/2014
7 Sat 22/02/2014

7 rows selected.

SQL> alter session set nls_territory=france;

Session altered.

SQL> select to_char(sysdate+level-2,'D Dy DD/MM/YYYY') from dual connect by level <= 7;
TO_CHAR(SYSDATE+
----------------
7 Sun 16/02/2014
1 Mon 17/02/2014
2 Tue 18/02/2014
3 Wed 19/02/2014
4 Thu 20/02/2014
5 Fri 21/02/2014
6 Sat 22/02/2014

7 rows selected.


Re: ORDER BY the day of the week [message #608192 is a reply to message #608187] Mon, 17 February 2014 04:26 Go to previous message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Thanks but that a bit complicated for me at this stage.
Previous Topic: FLASHBACK QUERY
Next Topic: Query to get Status of Students as 'Pass' or 'Fail' Using Date Condition
Goto Forum:
  


Current Time: Mon Oct 20 03:12:21 CDT 2014

Total time taken to generate the page: 0.08705 seconds