On Oct 15, 2:23 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> 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
David,
Very nice function, but remember there are other territories where
days of the week are
numbered differently:
Run this script:
ALTER SESSION SET NLS_TERRITORY='AMERICA';
SELECT to_char(SYSDATE,'D')
WeekDayNo,NLS_InitCap(to_char(SYSDATE,'DAY')) WeekDayname FROM dual
UNION
SELECT to_char(SYSDATE+1,'D'),NLS_InitCap(to_char(SYSDATE
+1,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+2,'D'),NLS_InitCap(to_char(SYSDATE
+2,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+3,'D'),NLS_InitCap(to_char(SYSDATE
+3,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+4,'D'),NLS_InitCap(to_char(SYSDATE
+4,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+5,'D'),NLS_InitCap(to_char(SYSDATE
+5,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+6,'D'),NLS_InitCap(to_char(SYSDATE
+6,'DAY'))FROM dual;
ALTER SESSION SET NLS_TERRITORY='IRELAND';
SELECT to_char(SYSDATE,'D')
WeekDayNo,NLS_InitCap(to_char(SYSDATE,'DAY')) WeekDayname FROM dual
UNION
SELECT to_char(SYSDATE+1,'D'),NLS_InitCap(to_char(SYSDATE
+1,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+2,'D'),NLS_InitCap(to_char(SYSDATE
+2,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+3,'D'),NLS_InitCap(to_char(SYSDATE
+3,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+4,'D'),NLS_InitCap(to_char(SYSDATE
+4,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+5,'D'),NLS_InitCap(to_char(SYSDATE
+5,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+6,'D'),NLS_InitCap(to_char(SYSDATE
+6,'DAY'))FROM dual;
ALTER SESSION SET NLS_TERRITORY='EGYPT';
SELECT to_char(SYSDATE,'D')
WeekDayNo,NLS_InitCap(to_char(SYSDATE,'DAY')) WeekDayname FROM dual
UNION
SELECT to_char(SYSDATE+1,'D'),NLS_InitCap(to_char(SYSDATE
+1,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+2,'D'),NLS_InitCap(to_char(SYSDATE
+2,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+3,'D'),NLS_InitCap(to_char(SYSDATE
+3,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+4,'D'),NLS_InitCap(to_char(SYSDATE
+4,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+5,'D'),NLS_InitCap(to_char(SYSDATE
+5,'DAY'))FROM dual
UNION
SELECT to_char(SYSDATE+6,'D'),NLS_InitCap(to_char(SYSDATE
+6,'DAY'))FROM dual;
Thomas
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
