Re: PL/SQL Removing weekends

In article <8s0s4m$fpd$1_at_nnrp1.deja.com>,   Atta ur-Rehman <atta707_at_my-deja.com> wrote:
> Hi Helen,
>
> the to_char() function with 'DY' format returns the 3 char name of the
> day for the given date:
>
> select to_char(sysdate, 'DY') fro dual;
>
> would return 'WED' on my machine.
>
> so if you wanna count the SATs and SUNs between two given dates try
> this one:
>
> select count(*)
> from your_table
> where your_date_field between to_date('01-JAN-2000', 'DD-MON-YYYY')
 and
> sysdate
> and to_char(your_date_field, 'DY') in ('SAT', 'SUN');
>
> that should return you the count of SATs and SUNs between 1 Jan 2k and
> todays date.
>
> HTH
>
> ATTA
>
> In article <8s0f5k$5s8$1_at_nnrp1.deja.com>,
> helen267_at_my-deja.com wrote:
> > Hi there
> >
> > I'm trying to write a simple PL/SQL script (for a stored procedure)
> > that will count the number of weekend days, i.e Saturday and Sunday,
> > that occur between two dates. I thought I had it right, but I can't
> > for the life of me work out why it's not working!
> >
> > Has anyone done anything similar and could help me out, either by
> > checking the script I have so far, or writing a quick solution?
> >
> > Thanks in advance
> >
> > Helen
> >

Helen, here is some code you may find of interest: rem
rem Find number of working, non-weekend, days in year to-date. rem
rem Fr Thomas Kyte post comp.database.oracle.misc 03/13/2000 rem create set 1..n on ref tbl that must have at least 366 rows rem keep rows such that first_day_of_year + ( runum - 1 ) not weekend rem
select count(*)
  from ( select trunc(sysdate,'year')+rnum-1

          from ( select rownum rnum
                    from all_objects
                  where rownum <= to_char(sysdate,'ddd') )
          where to_char( trunc(sysdate,'year')+rnum-1, 'd' )
                        not in ( '1', '7' )
      )

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.