Calculating Working Days WITHOUT USING A FUNCTION
.
If you need to calculate working day coun ,,,,,,,,,,,,,,,,,
SQL> SET LINESIZE 100
SQL> Desc Date_Test
Name NULL? TYPE
------------------------------------------------------------------------------------------ - ----------------
START_DT DATE
END_DT DATE
SQL> SELECT * from Date_Test
2 .
SQL> /
START_DT END_DT
--------- ---------
13-DEC-02 18-DEC-02
17-DEC-02 19-DEC-02
18-DEC-02 23-DEC-02
26-DEC-02 28-DEC-02
SQL> SELECT START_DT, END_DT, END_DT - START_DT AGE,
SQL> Work_DAYS (start_dt, end_dt) from date_test;
START_DT END_DT AGE WORK_DAYS (START_DT, END_DT)
---------------------------------------------- ----
13-DEC-02 18-DEC-02 5 3
17-DEC-02 19-DEC-02 2 2
18-DEC-02 23-DEC-02 5 3
26-DEC-02 28-DEC-02 2 1
SQL> Get WorkingDays
1 SELECT
2 START_DT,
3 end_dt,
4 trunc (end_dt - start_dt) agn,
5 (Trunc (End_DT - START_DT) -
6 (
Case
8 WHEN (8-to_Number (to_CHAR (START_DT, 'D')))> Trunc (End_DT - START_DT) 1
9 Then 0
10 else
11 Trunc ((Trunc (End_DT - Start_DT) - (8-to_Number (to_CHAR (START_DT, 'D'))))))) / 7) 1
12 END)
13 (CASE)
14 WHEN MOD (8-to_CHAR (START_DT, 'D'), 7)> Trunc (End_DT - START_DT) -115 Then 0
16 else
17 Trunc (Trunc (End_DT-START_DT) - (MOD (8-to_CHAR (START_DT, 'D'), 7) 1)) / 7) 1
18 END)
19)
20) WorkingDays
21 * from Date_Test
SQL> /
START_DT END_DT AGE WORKINGDAYS
-----------------------------------
13-DEC-02 18-DEC-02 5 3
17-DEC-02 19-DEC-02 2 2
18-DEC-02 23-DEC-02 5 3
26-DEC-02 28-DEC-02 2 1
Date / Time from Substraction of Two Date Values This tip comes from Galina Petrenko, Sr. IT Specialist, TPAS Systems Development, Towers Perrin in Voorhees, NJ. This technique presents conversion of the result of subtraction between dates. For example, I am looking for how many days, hours, minutes, etc. elapsed between two dates. Oracle Database is returning the result in days (such as 2.23456). The result is not exactly readable and has to be formatted. in most cases programmers convert the result using PL / SQL with different functions, and this is OK. But why not ask Oracle Database to do that with Oracle precision and speed? Please see the standard and the three "alternative" approaches below. For example, For example, after a job ends I Have That Job As Running:
5.12345 Days
DEF DIF = 5.12345
1. PL / SQL Approach:
--A Few Extra Variables Are include for the better viewing of temporary results
SET ServerOutput on
Declare
MONTHS NUMBER;
Days Number;
Hours Number;
Minutes Number;
Seconds Number;
DIFF NUMBER: = & DIF;
Diff0 Number;
Diff1 Number;
Diff2 Number;
Begin
Days: = floor (diff);
DIFF0: = MOD (DIFF, 1);
DBMS_OUTPUT.PUT_LINE ('days -' || days || 'mod is' || DIFF0); Hours: = floor (Diff0 * 24);
DIFF1: = MOD (DIFF0 * 24, 1);
DBMS_OUTPUT.PUT_LINE ('Hours -' || Hours || 'MOD 1 IS' || DIFF1);
Minutes: = floor (Diff1 * 60);
DIFF2: = MOD (Diff1 * 60, 1);
Seconds: = floor (Diff2 * 60);
DBMS_OUTPUT.PUT_LINE ('Minutes -' || minutes || 'Seconds -' || Seconds);
DBMS_OUTPUT.PUT_LINE ('Minutes -' || minutes || 'Seconds -' || Seconds);
END;
/
- OUTPUT:
Days - 5 mod IS .12345
Hours - 2 MOD 1 IS .9628
Minutes - 57 SECONDS - 46
Final: Days - 5 Hours - 2 Minutes - 57 SECONDS - 46
PL / SQL Procedure SuccessFully Completed.
2. SQL Method:
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) MSECONDS
From
(
SELECT
Trunc (& DIF) Days,
& DIF - TRUNC (& DIF) A
From dual
)
;
- OUTPUT:
Days a hours minutes seconds mseconds
---------- -------------------------------------- ------------
5.12345 2 57 46 8
3. Alternative approach ("nice-and-easy": - i will use a leap year date, 01/01/2000 for example, for teemporary purposes. - this date will provide account.
SELECT
TO_NUMBER (SUBSTR (A, 1, 4)) - 2000 Years,
TO_NUMBER (Substr (A, 6, 2)) - 01 MONTHS,
TO_NUMBER (Substr (A, 9, 2)) - 01 Days,
Substr (A, 12, 2) Hours,
Substr (A, 15, 2) Minutes,
Substr (A, 18, 2) Seconds
From
(
SELECT
TO_CHAR (To_Date ('20000101', 'YYYYMMDD') & DIF, 'YYYY MM DD HH24: MI: SS') A
From dual
)
;
Years Months Days Ho Mi SE
---------- ---------- ---------- - -
0 0 5 02 57 46
All Results Are Identical, But Think About Which Method Is Easiest. And Here Are More Examples: A. Def Dif = 14.28791.Final: Days - 14 Hours - 6 Minutes - 54 Seconds - 34 2.
Days a hours minutes seconds mseconds
---------- -------------------------------------- ------------
14.2879 6 54 34 56
3.
Years Months Days Ho Mi SE
---------- ---------- ---------- - -
0 0 14 06 54 35
B. DEF DIF = 351.1124 1.10: DAYS - 351 Hours - 2 Minutes - 41 Seconds - 51 2.
Days a hours minutes seconds mseconds
---------- -------------------------------------- ------------
351.1124 2 41 51 36
3.
Years Months Days Ho Mi SE
---------- ---------- ---------- - -
0 11 16 02 41 51
C. DEF DIF = 794.7734 1. FINAL: DAYS - 794 Hours - 18 Minutes - 33 Seconds - 41 2.
Days a hours minutes seconds mseconds
---------- -------------------------------------- ------------
794.7734 18 33 41 76
3.
Years Months Days Ho Mi SE
---------- ---------- ---------- - -
2 2 4 18 33 42
As you see, method # 3 (Alternative Approach ( "nice-and-easy")) is not the only alternative, but it is very representative and accurate with ALL calculations (done by Oracle Database). I used the first day of a Leap year especially for precision purposes.
