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