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;
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.