ORDER BY DECODE: Decode won’t reorder the days like I want it to

I’ve got an assignment where I need to return a certain report and then order it by the day, but I need Monday to be day #1 (by default, Sunday is day #1 in SQL). Here’s my code for the report and the output:

report without order by output without order by

I then add an order by decode, which runs and looks correct, but it doesn’t order the days properly:

report with order by output with order by

I don’t know what is going wrong, so if someone could help I’d really appreciate it, thanks!

Answer

You are not correct with the default day of the week in Oracle. It is not always Sunday, the documentation of the format D clearly states

Day of week (1-7). This element depends on the NLS territory of the session.

So what you need is only to set the in your session the NLS_TERRITORY to some country where the week starts with a Monday and you are done.

Maybe your setting is already so if you got this requirement, simple check with

select * from nls_session_parameters
where parameter like 'NLS_TERRITORY';

If not set a propper teritory for example with following statement

 alter session set nls_territory = 'UNITED KINGDOM';

Now you can order on the day of the week to_char(hire_date, 'd')

Example

select last_name, 
to_char(hire_date, 'DD-MON-RR') "HIRE_DAY",
to_char(hire_date, 'DAY') "DAY",
to_char(hire_date, 'd') day_no
from emp 
order by to_char(hire_date, 'd');

LAST_NAME    HIRE_DAY           DAY                                  D
------------ ------------------ ------------------------------------ -
Mr.Monday    01-MAR-21          MONDAY                               1
Mr.Tuesday   02-MAR-21          TUESDAY                              2
Mr.Wednesday 03-MAR-21          WEDNESDAY                            3
Mr.Thursday  04-MAR-21          THURSDAY                             4
Mr.Friday    05-MAR-21          FRIDAY                               5
Mr.Saturday  06-MAR-21          SATURDAY                             6
Mr.Sunday    07-MAR-21          SUNDAY                               7

Using day names for ordering is not always considered a best practice, because it makes you NLS_DATE_LANGUAGE dependent.

If you want a complete independent solution then use the IW trick. The calendar week is ISOso it works overall.

 order by 1 + TRUNC (hire_date) - TRUNC (hire_date, 'IW')