Ask the Expert

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.

    Requires Free Membership to View

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;

--------- -
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:

    in_date   DATE

--  ***************
--  **  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.

    return_text  CHAR (1) := TO_CHAR (in_date, 'D');
    IF  return_text = '1'
        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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: