Re: Oracle calendar teaser...

Try this:

set verify off;
SELECT
  trunc((trunc(to_date('&&end_day'),'DAY') -   trunc(to_date('&&start_day'),'DAY')) * 5/7 +   (to_date('&&end_day') - trunc(to_date('&&end_day'),'DAY')) -   (to_date('&&start_day') - trunc(to_date('&&start_day'),'DAY'))) Days FROM
  dual
/
undefine end_day
undefine start_day

DanHW (danhw_at_aol.com) wrote:
: >I have a little teaser for y'all. I want to write an SQL script (not
: >PL/SQL) that will tell me how many weekdays (Mon-Fri) there are between
: >2 dates. Can this be done and if so how?
: >
: >Thanks in advance.
: >
: >Martin
: >
: >
: >

: Perhaps someone will post a tested SQL command to do it; I do not have SQL*PLUS
: at home, so I can't write an test this. However, I can describe one way;
: hopefully it will be close enough that you can fix it if it is not correct.

: You can easily subtract the dates to get the number of days

: select d2-d1 from dual;

: Divide by 7 to get the number of weeks, then truncate to get the number of FULL
: weeks

: select int((d2-d1)/7) from dual

: Multiply by 5 to get workdays in each week

: select 5 *int((d2-d1)/7) from dual

: Now the tricky part - you need to get the fractional part of the week (this
: will add between 0 and 6 days). There is a date format code that returns the
: day of the week a day falls on, I think it is 'W'. Likewise, I don;t remember
: Oracle's convention, but suppose it is Sunday=0, Monday is 1,.. Saturday =6.

: If d1 falls on Sunday, and d2 falls on Monday (ie d1 is before d2 in the week),
: we will add the number of days between, which is d2[Monday]-d1[Sunday] = 1 - 0
: = 1

: If d1 falls on Tuesday, and d2 falls on Monday (id d1 is later in the week than
: d2), we need to add from the last Tuesday to Saturday [when the week starts]
: and then from Sunday to Monday. This gives us 6[Saturday]- 2[Tuesday] +
: 1[Monday] - 0[Sunday] = 6 - d1 - d2.

: Now you can put in a correction...
: w1=int(to_char(d1,'W')) and w2= same thing for d2

: decode(sgn(w2,w1),0,0,-1, 6-w1-w2,1,w2-w1)+(the stuff above)

: I did this once a few years ago at a previous job; I don't remember if I got it
: in a single select statement or not. Hopefully, this will get you on the right
: track.

: Dan Hekimian-Williams

--
______________________________________________________________________________
http://www.infinet.com/~cedwards 

Official home of RMBL! http://rmbl.ml.org
E-mail Don Ferry (DONFERRY_at_PRODIGY.NET) to be put on the waiting list.
Owner/GM/Manager of the Columbus Capitals
_______________________________________________________________________________