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 Received on Wed Dec 28 2005 - 04:26:26 CST