Weekday number regardless of the NLS settings

I’m looking for the simplest way to determine the weekday number for the DATE value in oracle independent of the NLS settings.

Monday  -> 1
Tuesday -> 2
…
Sunday  -> 7

Any ideas?

Answer

ISO weeks start on Monday; they don’t use NLS settings. I think this expression is reliable.

1 + trunc(your_date) - trunc(your_date, 'IW')

To show how the arithmetic works . . . current_date is a Wednesday.

select current_date as cur_date
     , trunc(current_date) as trunc_cur
     , trunc(current_date, 'IW') as trunc_iso
     , trunc(current_date) - trunc(current_date, 'IW') as date_diff
     , 1 + trunc(current_date) - trunc(current_date, 'IW') as dow
from dual

CUR_DATE                    TRUNC_CUR                  TRUNC_ISO                   DATE_DIFF  DOW
June 19 2013 16:01:51+0000  June 19 2013 00:00:00+0000 June 17 2013 00:00:00+0000  2          3

In general, if you can’t find an expression that reasonably does what you expect, you can always use a table. (And, perhaps, a function that isolates SQL from the underlying implementation.)

So you can always use something like

create table weekday_numbers (
  weekday char(3) not null primary key,
  weekday_num integer not null unique
    check(weekday_num between 1 and 7)
);

insert into weekday_numbers values ('Mon', 1);
...