1

F is a function counting the number of weekdays betwwen two dates (using the format to_date(a, 'DD/MM/YYYY') [NLS_DATE_LANGUAGE = "FRENCH"]. It is stored in package P.

With a is the beginning date of the period and b the end.

When I use the SQL developer to run the function i.e. Right click on package p, execute, choose f function and fill the arguments:

if a = to_date('02/08/2019', 'DD/MM/YYYY') and b = to_date('12/08/2019', 'DD/MM/YYYY'))

I get a number of days = 7, which is the correct result.

But when I run it in SQL:

select p.f('02/08/2019','12/08/2019') from DUAL

I get a number of days = 8 and this result is obviously wrong.

I cannot understand how these two process can return two different values since the same F function is called, and the arguments are exactly the same.

I have been careful about the date format, still the two ways of calling f won't return the same result.

For information, this is the content of this function (sorry, cariable are expressed in french)

FUNCTION getjoursouvres
(
    i_debut IN DATE,
    i_fin IN DATE   
)
RETURN NUMBER IS o_result NUMBER;
v_jour date;
v_nbjours NUMBER;
v_testferie number;
v_testweekend number;
v_testglobal number;
-- This cursor browses all dates between i_debut and i_fin (included)
cursor cx is 
            select to_date(i_debut, 'DD/MM/YYYY') + rownum -1 dt 
            from dual 
            connect by level <= to_date(i_fin, 'DD/MM/YYYY') - to_date(i_debut, 'DD/MM/YYYY') + 1;
BEGIN
open cx;
v_nbjours := 0;
    loop
-- Browses all the days in the interval (begining and end included)
        fetch cx into v_jour;
        exit when cx%NOTFOUND;
-- testferie return 1 if the day is NOT a holiday, 0 if it is (so not be be added)
            v_testferie := testferie(v_jour);
-- testweekend return 1 if the day is a weekday, 0 if it is on weekend (so not be be 
            v_testweekend := testweekend(v_jour);
-- 
            v_testglobal := v_testferie + v_testweekend;
-- If v_testglobal = 2 then the day is neither weekend nor holiday. Therefore it is aded to the sum of days
            if v_test = 2 then
                v_nbjours := v_nbjours + 1;
            end if;
    end loop;
o_result := v_nbjours;
close cx;
return o_result;
END;
2
  • 4
    Please could you edit your question to include the contents of your function f. Commented Aug 6, 2019 at 9:57
  • 2
    select f.p(a,b) from DUAL - what are a and b in that case? Commented Aug 6, 2019 at 9:57

1 Answer 1

1

After many calls of both way to use the function. I reach to the conclusion that the element inducing the anomaly comes from the ue of

to_date(v_date, 'DD/MM/YYYY')

where v_date is already declared as a date format behave in an inpredictable way. I decided to keep the date variable as such, and only use to_date for different cariable format.

I am not able to explain why exactly, but it solved my problems concerning dates behavior. Now I can call a function with data parameter from DUAL or from the SQL developer interface and get identical and coherent results.

Sign up to request clarification or add additional context in comments.

1 Comment

"where v_date is already declared as a date format" Did you mean declared as a date? A date doesn’t have any format.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.