Для определения дня недели по дате достаточно функции TO_CHAR с маской форматирования ‘D’. Возвращает номер дня недели (в диапазоне 1…7):
select to_char(sysdate, 'd') from dual;
Важное замечание: то, какой день недели считается первым, зависит от параметра инициализации сервера NLS_TERRITORY. Если NLS_TERRITORY=CIS (т.е. страны СНГ) — первым будет понедельник, а если, например, США — то воскресенье.