On Oct 17, 8:55 am, Thomas Olszewicki <[EMAIL PROTECTED]> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
I have done something similar with a query to return the next working
day from a given date, and it had to include holidays in the mix.
Thus a holidays table was created and in it is listed the holiday and
the date for several successive years. A function accesses this table
and computes the next actual working day from a given number of days
difference from the current date. I'm planning on modifying it to
accept a target date and report the actual date and the days
difference, but that's on the back burner. Your solution is probably
the best, given that you have multiple markets, in multiple world
locations, to service.
I thank you for your compliment. My goal is to help and educate. My
apologies if I occasionally present myself as stuffy or rude. It is
not intentional.
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
-~----------~----~----~----~------~----~------~--~---
