Q

Re-ordering weekdays

In Oracle, how do I ORDER BY the day of the week starting with Monday? When I use TO_CHAR (hiredate, 'Day') it

shows Friday, Monday, ..., Wednesday. ? When I use TO_CHAR (hiredate, 'D') it shows Sunday, Monday, ..., Saturday. When you sent this, the question and answer Fiscal year, ISO week had not yet been posted. Look on page 2 of that answer to see how NLS_TERRITORY can be used to make TO_CHAR (hiredate, 'D') do exactly what you want. If most of the people using your database consider Monday to be the first day of the week, you might convince the DBA that NLS_TERRITORY should be changed system-wide. Failing that, you can change it for individual sessions, perhaps in a log-in trigger for your application.

If you find NLS_TERRITORY isn't convenient for you, then think about what TO_CHAR (hiredate, 'D') is doing. It does something very close to what you want. The only problem is that it puts Sunday first, and you want Sunday to be last: all the other days are sorted correctly. Here's how you can get what you want:


SELECT DISTINCT  TO_CHAR (hiredate, 'Day')        AS weekday,
                 DECODE ( TO_CHAR (hiredate, 'D'),
                          '1',    '8',                 -- 1 -> 8
                          TO_CHAR (hiredate, 'D')      -- 2 -> 2, etc.
                        )                         AS d
FROM             emp
ORDER BY         2;

WEEKDAY   D
--------- -
Monday    2
Tuesday   3
Wednesday 4
Thursday  5
Friday    6
Saturday  7
Sunday    8

7 rows selected.

That DECODE statement isn't too hard to write once, but you don't want to write it twice, so encapsulate it in a function:


CREATE OR REPLACE FUNCTION  mday
(
    in_date   DATE
)
RETURN  CHAR

--  ***************
--  **  m d a y  **
--  ***************

--  mday returns a single character '2', '3', ..., '8'
--  that indicates in_date's day of the week
--  (Monday = '2').  If you sort by this value,
--  the days will be ordered Monday, Tuesday, ..., Sunday.

AS
    return_text  CHAR (1) := TO_CHAR (in_date, 'D');
BEGIN
    IF  return_text = '1'
    THEN
        return_text := '8';
    END IF;

    RETURN  return_text;
END   mday;
/

Now we can use this function to get the same results we got earlier:


SELECT DISTINCT  TO_CHAR (hiredate, 'Day')  AS weekday,
                 mday (hiredate)            AS d
FROM             emp
ORDER BY         2;

This was first published in October 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close