I’m looking for the simplest way to determine the weekday number for the DATE
value in oracle independent of the NLS
settings.
x
Monday -> 1
Tuesday -> 2
…
Sunday -> 7
Any ideas?
Advertisement
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);