How to return current hour of week?

One week have 168 hours.
How do you create SQl statement that return hour value for specific timestamp??
eg.
If week starts on system from Monday.
Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167
etc.
Ideas??This should be a good start:

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)].
Evaluation:
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)|||One more try...

(24 * DatePart("dw",'2004-03-8 1:00:00')-1) + {fn Hour('2004-03-8 1:00:00')}

Is it correct?|||You need another set of parenthesis for proper order of operations:

declare @.TimeStamp datetime
set @.TimeStamp = getdate()
select (24 * (DatePart(dw,@.TimeStamp)-1)) + {fn Hour(@.TimeStamp)}|||set datefirst 1
declare @.timestamp datetime
set @.timestamp = getdate()
select (datepart(dd,@.timestamp)-1)*24+datepart(hh,@.timestamp)|||Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time (http://webexhibits.org/daylightsaving/) then one week has 167 and one has 169 each year. If you need to keep Sidereal (http://tycho.usno.navy.mil/sidereal.html) 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.

-PatP|||declare @.dt datetime
set @.dt = '20040309 1:00'
select DATEDIFF(hh,DATEADD(d,-(DATEPART(w,@.dt)+@.@.DATEFIRST-2)%7,LEFT(@.dt,13)),@.dt)

Hans.|||Just like skinning a cat, there is more than one way to do this and whatever you choose, the result is not very attractive.