Weekday number regardless of the NLS settings

Question

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 1

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);
...