On Oct 16, 4:38 pm, Thomas Olszewicki <[EMAIL PROTECTED]> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
I didn't provide a 'solution', I provided an example, and examples are
intended to be modified to suit the recipients needs. Yes, I called
it a 'solution' in my post, and, well, we all make mistakes, this one
in nomenclature. It's an example, intended to be changed to meet the
requirements of the OP.
But, thanks for the interesting script.
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 Oracle-PLSQL@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
