Re: Supply Date based on DOW

Beware of using TO_CHAR({some date},'D'), it does not produce
consistent results.
It produces a value '1' to '7', but which day counts as day 1 depends
on NLS settings:

ALTER SESSION SET NLS_TERRITORY='UNITED KINGDOM';
SELECT TO_CHAR(SYSDATE,'D'), TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY') FROM
DUAL;
3 Wed 28-Dec-2005

ALTER SESSION SET NLS_TERRITORY=FRANCE;
SELECT TO_CHAR(SYSDATE,'D'), TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY') FROM
DUAL;
3 Wed 28-Dec-2005

ALTER SESSION SET NLS_TERRITORY=GERMANY;
SELECT TO_CHAR(SYSDATE,'D'), TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY') FROM
DUAL;
3 Wed 28-Dec-2005

ALTER SESSION SET NLS_TERRITORY=AMERICA;
SELECT TO_CHAR(SYSDATE,'D'), TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY') FROM
DUAL;
4 Wed 28-Dec-2005

I would recommend NOT using it, unless you can guarantee that all the
users running your code have the same NLS settings.

The following does produce Monday = 1 to Sunday = 7 consistently (3rd
Jan 2000 was a Monday), though I admit it is a kludge.:
TRUNC(MOD(SYSDATE-TO_DATE('20000103','YYYYMMDD'),7))+1

.