On Oct 17, 7:59 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
David,
I didn't have any intention to correct your function. I read all your
posts and I learn a lot
from all of them. So thank you very much for all your suggestions.
This thread got my attention mainly because this is real life problem
we are facing
with our application in different regions of the world.
Just to add to the "problem", there is another twist with "working
days" in different countries.
In different territories, different days of the week are considered
"non working days".
For example: in Israel it is Saturday only, some other places it is
Friday and Saturday
I didn't find any Oracle NLS settings for "non working days", so we
end up creating a
new table where we store working, not working days settings for each
region we support.
If anybody does have a better solution, I'd love to hear about it.
Thank you again
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 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
-~----------~----~----~----~------~----~------~--~---
