Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> confusing date question

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
--======================================================================


   WhatDate	DATE;
   PassedDay	NUMBER;
   NextMonday	DATE;
   

BEGIN

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US