OraFAQ Forum: SQL & PL/SQL » ORDER BY the day of the week

Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY the day of the week (ORACLE DB SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORDER BY the day of the week [message #608179]

Mon, 17 February 2014 03:49

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;

Report message to a moderator

Re: ORDER BY the day of the week [message #608182 is a reply to message #608179]

Mon, 17 February 2014 04:01

cookiemonster
Messages: 13920
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

Report message to a moderator

Re: ORDER BY the day of the week [message #608184 is a reply to message #608182]

Mon, 17 February 2014 04:09

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;

Report message to a moderator

Re: ORDER BY the day of the week [message #608185 is a reply to message #608179]

Mon, 17 February 2014 04:10

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: ORDER BY the day of the week [message #608187 is a reply to message #608184]

Mon, 17 February 2014 04:16

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: ORDER BY the day of the week [message #608192 is a reply to message #608187]

Mon, 17 February 2014 04:26

hans_cellc
Messages: 11
Registered: February 2014

Junior Member

Thanks but that a bit complicated for me at this stage.

Report message to a moderator

Previous Topic:	FLASHBACK QUERY
Next Topic:	Query to get Status of Students as 'Pass' or 'Fail' Using Date Condition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 08:53:54 CDT 2024