Date Functions

Date Calculations

Returns A Day A Specified Number Of Days In The Future Skipping Weekends

CREATE OR REPLACE FUNCTION business_date (start_date DATE,
Days2Add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
BEGIN
WHILE Counter < Days2Add LOOP
CurDate := CurDate+1;
DayNum := TO_CHAR(CurDate, 'D');

IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;
RETURN start_date + Counter + SkipCntr;
END business_date;
/


Business Date function, above, enhanced by Larry Benton to handle negative values for the days2add parameter.

CREATE OR REPLACE FUNCTION business_date (start_date DATE,
days2add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
Direction INTEGER := 1; -- days after start_date
BusinessDays NUMBER := Days2Add;
BEGIN
IF Days2Add < 0 THEN
Direction := - 1; -- days before start_date
BusinessDays := (-1) * BusinessDays;
END IF;

WHILE Counter < BusinessDays LOOP
CurDate := CurDate + Direction;
DayNum := TO_CHAR( CurDate, 'D');

IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;

RETURN start_date + (Direction * (Counter + SkipCntr));
END business_date;
/

Returns The First Day Of A Month


CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/

Time Calculations

CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/

Calculating time from seconds

SELECT DECODE(FLOOR(999999/86400), 0, '',
FLOOR(999999/86400) || ' day(s), ') ||
TO_CHAR(TO_DATE(MOD(999999, 86400),'SSSSS'), 'HH24:MI:SS') AS elapsed
FROM dual;