On Oct 13, 10:57 am, Vlad <[EMAIL PROTECTED]> wrote:
> I know that datediff can be used to calculate the number of calendar
> days difference between two dates - problem is that I need to know the
> number of working days between two dates.
>
> It would probably be acceptable for me to have the number of weekdays
> between two dates.
>
> Any ideas anyone.
>
> TIA
datediff is a Transact-SQL (T-SQL) function, not an Oracle PL/SQL
offering. Are you certain you've posted this to the correct
newsgroup?
For a PL/SQL solution to this:
SQL> create or replace function workdays_between_dates(p_dt1 varchar2,
p_dt2 varchar2)
2 return number
3 is
4 v_date_high date:= greatest(to_date(p_dt1, 'DD-MON-
RRRR'),to_date(p_dt2, 'DD-MON-RRRR'
5 v_date_low date:= least(to_date(p_dt1, 'DD-MON-
RRRR'),to_date(p_dt2, 'DD-MON-RRRR'));
6 v_day_ctr number:=0;
7 begin
8 while (v_date_high >= v_date_low)
9 loop
10 if to_number(to_char(v_date_low, 'D')) between 2
and 6 then
11
12 v_day_ctr := v_day_ctr + 1;
13
14 v_date_low := v_date_low + 1;
15
16 elsif to_number(to_char(v_date_low, 'D')) = 7
then
17
18 v_date_low := v_date_low + 2;
19
20 elsif to_number(to_char(v_date_low, 'D')) = 1
then
21
22 v_date_low := v_date_low + 1;
23
24 end if;
25
26 end loop;
27
28 return v_day_ctr;
29
30 end;
31 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> select workdays_between_dates('12-OCT-2008', '18-OCT-2008') from
dual;
WORKDAYS_BETWEEN_DATES('12-OCT-2008','18-OCT-2008')
---------------------------------------------------
5
SQL> select workdays_between_dates('12-OCT-2008', '19-OCT-2008') from
dual;
WORKDAYS_BETWEEN_DATES('12-OCT-2008','19-OCT-2008')
---------------------------------------------------
5
SQL> select workdays_between_dates('12-OCT-2008', '26-OCT-2008') from
dual;
WORKDAYS_BETWEEN_DATES('12-OCT-2008','26-OCT-2008')
---------------------------------------------------
10
SQL> select workdays_between_dates('01-APR-2008', '26-OCT-2008') from
dual;
WORKDAYS_BETWEEN_DATES('01-APR-2008','26-OCT-2008')
---------------------------------------------------
149
SQL>
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
