DECLARE @startDate SMALLDATETIME
DECLARE @endDate SMALLDATETIME
--Set Test value for Thursday Feb 22nd
SET @endDate = ('02/22/2004 15:21:20')
--Set the start date to first day of the week
SET @startDate = DATEADD(Day, -(DATEPART(WeekDay, @endDate) - 1), @endDate)
--Remove the time component of the start date
SET @startDate = CONVERT(VARCHAR(10), @startDate, 101)
--Count the number of hours from start of week (Mon) to @endDate
SELECT DATEDIFF(Hour, @startDate, @endDate)
This could be converted to run as part of a select where @endDate is provided by a column with Date data. If you use this verbatim you will want to test boundary conditions to assure they meet your requirements and I'm sure it could be optimized.
The key is in the setting of @startDate [DATEADD(Day, -(DATEPART(WeekDay, @endDate) - 1), @endDate)].
DATEPART(WeekDay, @EndDate) = Day Code for Thursday (4)
(4) - 1 = 3*
-(3) = -3
DATEADD(Day, (-3), @endDate) = Sets Date To Monday (*determined by the 1 subtracted from the original day code)
Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time then one week has 167 and one has 169 each year. If you need to keep Sidereal time, then a different week each year can have a smidgeon more or less than 168.
These don't affect everyone, but they are the basis behind my always using date functions instead of "roll your own" functions like these. I'm not exactly sure how I'd handle this case, but I just wanted to raise the issue before folks go merrily trooping off with a solution that might not always work for them.