| Arnie Rowland 2006-10-28, 7:14 pm |
| 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.googlegroups.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
>
|