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?
JimHere 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.goog
legroups.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
>