Number of Days Of Week

Hi,


I need to find the number of Mondays within a given date range.

Example:  How many Mondays between 01/01/2006 and 06/30/2006.

Any suggestions on how to do this?


Jim

Here is a function that will do this for you.

CREATE FUNCTION dbo.fnDaysBetween
(  @StartDate  datetime,
@EndDate    datetime,
@ConcernDay char(2)   -- MO, TU, WE, etc.
)
RETURNS int
AS
BEGIN

DECLARE @Calendar table
( CalendarDate smalldatetime )

WHILE ( @StartDate <= @EndDate )
BEGIN
INSERT INTO @Calendar
SELECT @StartDate
SET @StartDate = dateadd( day, 1, @StartDate )
END

RETURN
(  isnull(( 
SELECT DayCount
FROM ( SELECT 
DayCount = count(1), 
DayName  = datename( dw, CalendarDate )
FROM @Calendar
GROUP BY datename( dw, CalendarDate )
) dt
WHERE upper( left( DayName, 2 )) = @ConcernDay
), 0 )
)
END
GO


SELECT dbo.fnDaysBetween( '10-01-2006', '11-30-2006', 'mo' )
SELECT dbo.fnDaysBetween( '10-01-2006', '11-30-2006', 'fr' )
SELECT dbo.fnDaysBetween( '10-06-2006', '10-10-2006', 'fr' )
SELECT dbo.fnDaysBetween( '10-06-2006', '10-10-2006', 'we' )


DROP FUNCTION dbo.fnDaysBetween

-- 
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience. 
Most experience comes from bad judgment. 
- Anonymous

You can't help someone get up a hill without getting a little closer to the 
top yourself.
- H. Norman Schwarzkopf


<Jim.Mueksch@wellsfargo.com> wrote in message news:1161982262.413737.288480@k70g2000cwa.goo
glegroups.com...
> Hi,
> 
> 
>        I need to find the number of Mondays within a given date range.
> 
>        Example:  How many Mondays between 01/01/2006 and 06/30/2006.
> 
>        Any suggestions on how to do this?
> 
> 
> Jim
>