How to extract weekends ie Saturdays and Sundays in a given Date range

[MUIGAIPCCL]

I am trying to exract weekends given a date range in MS visual foxpro and I almost hitting s deadrock
The situation is like date1=Ctod('01/10/2003') and date2=ctod('25/11/2003') I want to get the number of days which are Saturdays and the number of days which are Sundays in between date1 and date2

Whoever has a solution please help

 

[Mike Gagnon]

This might work for you

SET date dmy
date1 = Ctod('01/10/2003')
date2 = ctod('25/11/2003')
nCount = 0
date3 = date1
DO WHILE date3 < ctod('25/11/2003')
  IF DOW(date3) = 7 OR DOW(date3) = 6
    nCount = nCount + 1
  ENDIF
  date3 = date3 + 1
ENDDO
WAIT WINDOW &quot;There are &quot;+TRANSFORM(nCount) +&quot; between those two dates&quot;

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[alvechurchdata]

SQL might give a faster answer:

Select count(*) ;
from mytable ;
where dow(date3)=6 ;
into array SatCount

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Gagnon]

Geoff

Your solution only accounts for Saturdays.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[wgcs]

Geoff and Mike demonstrate solutions to two different situations...
It seems that you, MUIGAIPCCL, just need a count, in which Mike's suggestion will be blindingly fast, and provides what you need....

Unless, as Geoff is guessing, you're not counting the number of saturdays/sundays, but rather the number of records on saturdays/sundays... in which case the code for both Sat & sun would be:

Select count(*) ;
  from mytable ;
  where dow(date3)=6 or dow(date3)=0 ;
  into array WkndDayCount

This only gives the total Sat+sun... for separate numbers:

Select TOT( iif(dow(date3)=6, 1, 0) ) AS SatRecs, ;
  TOT( iif(dow(date3)=0, 1, 0) ) AS SunRecs ;
  from mytable ;
  into array WkndDayCount 
WkndDayCount[1] = number of saturday records
WkndDayCount[2] = number of sunday records