confusing date question

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 22 Jul 2002 17:20:43 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702671929@lnewton.leeds.lfs.co.uk>

Cynic,

CREATE OR REPLACE FUNCTION next_working_day(in_date IN VARCHAR2, date_format IN VARCHAR2)
RETURN DATE
AS
--======================================================================

---

• This function takes a date (and format) and determines if that date is a Saturday or
• Sunday, if so, it returns a date for the following Monday, otherwise it simply
• returns the date passed in.
  --
  
• IN_DATE : Some text representing a date - any format is allowed.
• DATE_FORMAT : Determines the format of the passed in date.
  --======================================================================
  

  ---

   WhatDate	DATE;
   PassedDay	NUMBER;
   NextMonday	DATE;
   

BEGIN

• Convert the passed in text to a date - the format should match that supplied WhatDate := TO_DATE(in_date, date_format);
• Extract the day of the week
• Saturday is day 6, Sunday is day 7 SELECT TO_CHAR(WhatDate, 'D') INTO PassedDay FROM dual;
• Was the passed day a weekend day ?
• If so, get Monday's date and return it. IF ((PassedDay = 6) OR (PassedDay = 7)) THEN RETURN (NEXT_DAY(WhatDate, 'MONDAY')); ELSE RETURN WhatDate; END IF; END; /

Beware, you could use NEXT_DAY on your own dates, but if the date was a Monday-Friday then you would get the date for the FOLLOWING Monday returned - which isn't what you want.

Some examples :

SELECT TO_CHAR(TO_DATE('19/07/2002', 'DD/MM/YYYY'),'DAY') FROM dual TO_CHAR(TO_DATE('19/07/2002','

---

FRIDAY
1 row selected

SELECT next_working_day('19/07/2002', 'DD/MM/YYYY') FROM dual NEXT_WORKING_DAY('19/07/2002',

---

19/07/2002
1 row selected

SELECT TO_CHAR(TO_DATE('20/07/2002', 'DD/MM/YYYY'),'DAY') FROM dual TO_CHAR(TO_DATE('20/07/2002','

---

SATURDAY
1 row selected

SELECT next_working_day('20/07/2002', 'DD/MM/YYYY') FROM dual NEXT_WORKING_DAY('20/07/2002',

---

22/07/2002
1 row selected

SELECT TO_CHAR(TO_DATE('21/07/2002', 'DD/MM/YYYY'),'DAY') FROM dual TO_CHAR(TO_DATE('21/07/2002','

---

SUNDAY
1 row selected

SELECT next_working_day('21/07/2002', 'DD/MM/YYYY') FROM dual NEXT_WORKING_DAY('21/07/2002',

---

22/07/2002
1 row selected

SELECT TO_CHAR(TO_DATE('22/07/2002', 'DD/MM/YYYY'),'DAY') FROM dual TO_CHAR(TO_DATE('22/07/2002','

---

MONDAY
1 row selected

SELECT next_working_day('22/07/2002', 'DD/MM/YYYY') FROM dual NEXT_WORKING_DAY('22/07/2002',

---

22/07/2002
1 row selected

HTH. Regards,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

Received on Mon Jul 22 2002 - 11:20:43 CDT